def getInvalidRPT01ProdPosList(strEDIConnection, strEDIDB):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM " + \
" , SYSTEM_PROD_CODE, SYSTEM_PROD_NM " + \
" , POS_DATE_MIN, POS_DATE_MAX " + \
"FROM DMMDL.DC0024_INVALID_RPT01_PROD_POS " + \
"ORDER BY KA_SYSTEM_CODE, POS_DATE_MIN, POS_DATE_MAX " + \
"; "
listInvalidRPT01ProdPos = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listInvalidRPT01ProdPos
def updateFlowEndData(strEDIConnection, strEDIDB, strEDINo, enumFlowResult):
strSQL = "UPDATE DWWANT.EDI_FLOW SET " + \
" {STATUS_ID}, {RESULT_ID}, {DT_END} " + \
" , {UPD_USER_ID}, UPD_DT=NOW() " + \
"WHERE {EDI_NO}; "
enumEDIResultID = EDIEnum.EnumEDIResultID.F
if enumFlowResult == EDIEnum.EnumFlowResult.Success:
enumEDIResultID = EDIEnum.EnumEDIResultID.S
elif enumFlowResult == EDIEnum.EnumFlowResult.Cancel:
enumEDIResultID = EDIEnum.EnumEDIResultID.C
dictMariaParameter = {
"STATUS_ID":
MariaEntity.MariaParameter(
strOperator="=",
objValue=EDIEnum.EnumEDIStatusID.F.name,
enumType=MariaEntity.EnumMariaParameterType.Char),
"RESULT_ID":
MariaEntity.MariaParameter(
strOperator="=",
objValue=enumEDIResultID.name,
enumType=MariaEntity.EnumMariaParameterType.Char),
"DT_END":
MariaEntity.MariaParameter(
strOperator="=",
objValue=datetime.datetime.now(),
enumType=MariaEntity.EnumMariaParameterType.DateTime),
"UPD_USER_ID":
MariaEntity.MariaParameter(
strOperator="=",
objValue="EDIService",
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_NO":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strEDINo,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
return DBEntity.executeMariaData(strEDIConnection, strEDIDB, strSQL)
def getWaitingFlowList(strEDIConnection, strEDIDB, strEDIID):
strSQL = "SELECT M.* " + \
"FROM DWWANT.EDI_FLOW M " + \
"JOIN (SELECT EDI_NO " + \
" FROM DWWANT.EDI_FLOW " + \
" WHERE {EDI_ID} AND STATUS_ID='W' AND IS_DELETED='N') G " + \
"ON M.EDI_NO=G.EDI_NO " + \
"ORDER BY M.EDI_NO; "
dictMariaParameter = {
"EDI_ID":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strEDIID,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listWaitingFlows = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listWaitingFlows
def getStoreMatlMSystemList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT KA_SYSTEM_CODE, MAX(KA_SYSTEM_NM) AS KA_SYSTEM_NM " + \
" , CONCAT(MAX(KA_SYSTEM_CODE), '-', MAX(KA_STORE_CODE)) AS STORE_CODE " + \
" , MAX(POS_YM) AS POS_YM " + \
"FROM DMMDL.DC0024_STORE_MATL_M " + \
"WHERE {DATA_DATE} " + \
"GROUP BY KA_SYSTEM_CODE " + \
"ORDER BY KA_SYSTEM_CODE; "
dictMariaParameter = {
"DATA_DATE":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strDataDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listStoreMatlMSystem = DBEntity.getMariaDataList(strEDIConnection,
strEDIDB, strSQL)
return listStoreMatlMSystem
def insertNextFlow(strEDIConnection, strEDIDB, strEDIDate, strEDIID,
strEDIFlowID, strAutoEDINo, strDataDate):
strSQL = "INSERT INTO DWWANT.EDI_FLOW VALUES ( " + \
" DWWANT.FN_GET_EDI_NO({EDI_DATE}), {EDI_ID}, {EDI_FLOW_ID} " + \
" , NULL, NULL " + \
" , {DATA_DATE}, 'W', NULL, NULL, NULL " + \
" , 'Y', NOW(), {AUTO_EDI_NO}, 'N' " + \
" , {UPD_USER_ID}, NOW() " + \
"); "
dictMariaParameter = {
"EDI_DATE":
MariaEntity.MariaParameter(
objValue=strEDIDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_ID":
MariaEntity.MariaParameter(
objValue=strEDIID,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_FLOW_ID":
MariaEntity.MariaParameter(
objValue=strEDIFlowID,
enumType=MariaEntity.EnumMariaParameterType.Char),
"DATA_DATE":
MariaEntity.MariaParameter(
objValue=strDataDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
"AUTO_EDI_NO":
MariaEntity.MariaParameter(
objValue=strAutoEDINo,
enumType=MariaEntity.EnumMariaParameterType.Char),
"UPD_USER_ID":
MariaEntity.MariaParameter(
objValue="EDIService",
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
return DBEntity.executeMariaData(strEDIConnection, strEDIDB, strSQL)
def getDataCountDifJobList(strEDIConnection, strEDIDB, strEDINo, strJobID):
strSQL = "SELECT * " + \
"FROM DWWANT.EDI_JOB " + \
"WHERE {EDI_NO} AND {EDI_JOB_ID} AND IFNULL(DATA_COUNT_DIF,-1)>0; "
dictMariaParameter = {
"EDI_NO":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strEDINo,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_JOB_ID":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strJobID,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listDataCountDifJobs = DBEntity.getMariaDataList(strEDIConnection,
strEDIDB, strSQL)
return listDataCountDifJobs
def insertNewFlow(strEDIConnection, strEDIDB, strEDIDate, strEDIID,
strEDIFlowID, strDataDate):
arrayArguments = [
strEDIID, strEDIFlowID, strEDIDate, strDataDate, "EDIService", "N"
]
strSPResult = DBEntity.executeMariaProcedure(strEDIConnection, strEDIDB,
"SP_EDI_INSERT_NEW_FLOW",
arrayArguments)
if strSPResult == "Y":
return True
else:
return False
def getStoreMatlDList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM " + \
" , CONCAT(KA_SYSTEM_CODE, '-', KA_STORE_WH_CODE) AS KA_STORE_WH_CODE, KA_STORE_WH_NM, STORE_WH_TYPE_NM " + \
" , SALES_COM_ABR_SA, SALES_COM_ABR_WH " + \
" , PROD_H1_NM, PROD_H2_NM, PROD_MATL_ID, PROD_MATL_NM " + \
" , POS_INV_DATE, POS_QTY_PCS, POS_QTY_PKG, POS_AMT, INV_QTY_PCS, INV_QTY_PKG " + \
"FROM DMMDL.DC0024_STORE_MATL_D " + \
"WHERE {DATA_DATE} " + \
"ORDER BY KA_SYSTEM_CODE, KA_STORE_WH_CODE, PROD_H1_ID, PROD_H2_ID, PROD_MATL_ID; "
dictMariaParameter = {
"DATA_DATE":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strDataDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listStoreMatlD = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listStoreMatlD
def getStoreMList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM " + \
" , CONCAT(KA_SYSTEM_CODE, '-', KA_STORE_CODE) AS STORE_CODE, KA_STORE_NM " + \
" , SALES_COM_ABR_SA, SALES_COM_ABR_WH " + \
" , CONCAT(LEFT(POS_YM,4), '/', RIGHT(POS_YM,2)) AS POS_YM " + \
" , POS_QTY_PCS_D, POS_QTY_PKG_D, POS_AMT_D " + \
" , POS_QTY_PCS_M, POS_QTY_PKG_M, POS_AMT_M " + \
"FROM DMMDL.DC0024_STORE_M " + \
"WHERE {DATA_DATE} " + \
"ORDER BY KA_SYSTEM_CODE, STORE_CODE; "
dictMariaParameter = {
"DATA_DATE":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strDataDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listStoreM = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listStoreM
def getEMPSalesList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT EMP_ID, EMP_NM, EMP_GENDER_NM " + \
" , ONBOARD_YMD, SENIORITY " + \
" , HR_COM_NM, HR_OFF_NM " + \
" , EMP_POS_NM, EMP_POS_PROP_NM, EMP_POS_TYPE_NM, EMP_POS_FLAG " + \
" , EMP_POS_TITLE_NM, EMP_POS_LEVEL_ID, EMP_POS_LEVEL_NM " + \
"FROM DMMDL.DC0022_EMP_SALES " + \
"WHERE {DATA_DATE} " + \
"ORDER BY EMP_POS_PROP_ID, HR_COM_ID, HR_OFF_ID, EMP_POS_LEVEL_ID; "
dictMariaParameter = {
"DATA_DATE":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strDataDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listEMPSales = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listEMPSales
def getCustStmtList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT SALES_CREDIT_ID, SALES_CREDIT_DESC " + \
" , WANT_COM_NM, SALES_OFF_NM " + \
" , (TODAY_AMT/1000) AS TODAY_AMT " + \
" , (BAL_BEGIN/1000) AS BAL_BEGIN " + \
" , (CUM_AMT/1000) AS CUM_AMT " + \
" , (SALES_AMT/1000) AS SALES_AMT " + \
" , (BAL_END/1000) AS BAL_END " + \
"FROM DMMDL.DC0021_CUST_STMT " + \
"WHERE {DATA_DATE} AND RPT_YM=SUBSTR(DATA_DATE, 1, 6) " + \
"ORDER BY SALES_CREDIT_ID, WANT_COM_ID, SALES_OFF_ID; "
dictMariaParameter = {
"DATA_DATE":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strDataDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listCustStmt = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listCustStmt
def getCustSalesList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT WANT_CHAN_NM, WANT_COM_NM " + \
" , PROD_H1_NM, PROD_H2_NM, PROD_H3_NM " + \
" , (POSTED_AMT_LAST_ALL/1000) AS POSTED_AMT_LAST_ALL " + \
" , (POSTED_AMT_LAST/1000) AS POSTED_AMT_LAST " + \
" , (POSTED_AMT_THIS/1000) AS POSTED_AMT_THIS " + \
" , (POSTING_AMT/1000) AS POSTING_AMT " + \
"FROM DMMDL.DC0021_CUST_SALES " + \
"WHERE {DATA_DATE} AND RPT_YM=SUBSTR(DATA_DATE, 1, 6) " + \
"ORDER BY WANT_CHAN_ID, WANT_COM_ID " + \
" , PROD_H1_ID, PROD_H2_ID, PROD_H3_ID; "
dictMariaParameter = {
"DATA_DATE":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strDataDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listCustSales = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listCustSales