def getOrdrTrasDList(strEDIConnection, strEDIDB, strDataDate, strSystemOrdrYM):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM " + \
" , SYSTEM_ORDR_DATE " + \
" , ORDR_MAIN_ID, SYSTEM_ORDR_NO, SAP_ORDR_NO " + \
" , OPEN_ORDR_DATE " + \
" , CUST_ID " + \
" , SALES_COM_ID_DE_ORDR, SALES_COM_ABR_DE_ORDR, SALES_COM_ID_DE, SALES_COM_ABR_DE, SALES_COM_ID_BL, SALES_COM_ABR_BL " + \
" , SALES_CHAN_ID, PROD_DIV_ID " + \
" , SYSTEM_DEV_DATE, IS_FACT_DEV " + \
" , ORDR_LINE_NUM, SAP_LINE_NUM " + \
" , SYSTEM_ORDR_PROD_NM " + \
" , PROD_H1_ID_SYS, PROD_H1_NM_SYS, PROD_H2_ID_SYS, PROD_H2_NM_SYS " + \
" , PROD_MATL_ID_SYS, PROD_MATL_NM_SYS " + \
" , PROD_H1_ID_OPEN, PROD_H1_NM_OPEN, PROD_H2_ID_OPEN, PROD_H2_NM_OPEN " + \
" , PROD_MATL_ID_OPEN, PROD_MATL_NM_OPEN " + \
" , STORE_PLACE_CODE, IS_STORE_PLACE_DIR " + \
" , PROD_AMT_PKG_SYS, PROD_QTY_PKG_SYS, PROD_AMT_PKG_OPEN, PROD_QTY_PKG_OPEN " + \
" , SAP_AMT_PKG_OPEN, SAP_QTY_PKG_OPEN, SAP_AMT_PKG_APPD, SAP_QTY_PKG_APPD " + \
" , SHIP_QTY_PKG_DIF, SHIP_QTY_PKG_DEV, SHIP_QTY_PKG_ACT " + \
"FROM DMMDL.DC0024_ORDR_TRAS_D " + \
"WHERE LEFT(SYSTEM_ORDR_DATE, 6)={SYSTEM_ORDR_YM} " + \
"ORDER BY KA_SYSTEM_CODE, SYSTEM_ORDR_DATE, SYSTEM_ORDR_NO, ORDR_LINE_NUM; "
dictMariaParameter = {
"SYSTEM_ORDR_YM":
MariaEntity.MariaParameter(
objValue=strSystemOrdrYM,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listOrdrTrasD = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listOrdrTrasD
def updateJobCountData(strEDIConnection, strEDIDB, strEDINo, strJobID,
intDataCount, intDataVolume):
strSQL = "UPDATE DWWANT.EDI_JOB J " + \
"LEFT JOIN DWWANT.EDI_JOB D " + \
"ON J.EDI_NO=D.EDI_NO AND J.EDI_DEP_JOB_ID=D.EDI_JOB_ID " + \
"SET J.DATA_COUNT={DATA_COUNT}, J.DATA_VOLUME={DATA_VOLUME} " + \
" , J.DATA_COUNT_DIF=(CASE WHEN J.EDI_JOB_TYPE='MariaDBImport' THEN (IFNULL(D.DATA_COUNT,0)-{DATA_COUNT}) ELSE NULL END) " + \
" , J.UPD_USER_ID={UPD_USER_ID}, J.UPD_DT=NOW() " + \
"WHERE J.EDI_NO={EDI_NO} AND J.EDI_JOB_ID={EDI_JOB_ID}; "
dictMariaParameter = {
"DATA_COUNT":
MariaEntity.MariaParameter(
objValue=intDataCount,
enumType=MariaEntity.EnumMariaParameterType.Int),
"DATA_VOLUME":
MariaEntity.MariaParameter(
objValue=intDataVolume,
enumType=MariaEntity.EnumMariaParameterType.Int),
"UPD_USER_ID":
MariaEntity.MariaParameter(
objValue="EDIService",
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_NO":
MariaEntity.MariaParameter(
objValue=strEDINo,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_JOB_ID":
MariaEntity.MariaParameter(
objValue=strJobID,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
return DBEntity.executeMariaData(strEDIConnection, strEDIDB, strSQL)
def getOrdrSysDList(strEDIConnection, strEDIDB, strDataDate, strSystemOrdrYM):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM " + \
" , SYSTEM_ORDR_DATE " + \
" , ORDR_MAIN_ID, SYSTEM_ORDR_NO " + \
" , SALES_COM_ID_DE, SALES_COM_ABR_DE, SALES_COM_ID_BL, SALES_COM_ABR_BL " + \
" , SALES_CHAN_ID, PROD_DIV_ID " + \
" , CUST_ID " + \
" , SYSTEM_DEV_DATE" + \
" , ORDR_LINE_NUM " + \
" , SYSTEM_ORDR_PROD_NM " + \
" , PROD_H1_ID, PROD_H1_NM, PROD_H2_ID, PROD_H2_NM " + \
" , PROD_MATL_ID, PROD_MATL_NM " + \
" , PROD_AMT_PKG, TRAS_PROD_AMT_PKG, PROD_QTY_PKG " + \
" , SAP_QTY_PKG_APPD_SUM_DIR, SAP_QTY_PKG_APPD_SUM, SAP_QTY_PKG_APPD_SUM_DIF " + \
" , SHIP_QTY_PKG_DIF_SUM, SHIP_QTY_PKG_DEV_SUM, SHIP_QTY_PKG_ACT_SUM, SHIP_QTY_PKG_ACT_SUM_DIF " + \
" , TRAS_RETURN_PHASE_NM, TRAS_RETURN_RMK " + \
"FROM DMMDL.DC0024_ORDR_SYS_D " + \
"WHERE LEFT(SYSTEM_ORDR_DATE, 6)={SYSTEM_ORDR_YM} " + \
"ORDER BY KA_SYSTEM_CODE, SYSTEM_ORDR_DATE, SYSTEM_ORDR_NO, ORDR_LINE_NUM; "
dictMariaParameter = {
"SYSTEM_ORDR_YM":
MariaEntity.MariaParameter(
objValue=strSystemOrdrYM,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listOrdrSysD = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listOrdrSysD
def getPOSRPT01List(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT POS_DATE, DWWANT.FN_GET_WEEK_LAST_DATE(POS_DATE) AS WEEK_LAST_DATE " + \
"FROM DMMDL.DC0024_POS_RPT01 " + \
"WHERE {DATA_DATE}; "
dictMariaParameter = {
"DATA_DATE": MariaEntity.MariaParameter(strOperator="=", objValue=strDataDate, enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listPOSRPT01 = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listPOSRPT01
def getInvalidRPT01List(strEDIConnection, strEDIDB, strEDINo):
strSQL = "SELECT POS_DATE_MIN, POS_DATE_MAX " + \
"FROM DMMDL.DC0024_INVALID_RPT01 " + \
"WHERE {EDI_NO}; "
dictMariaParameter = {
"EDI_NO": MariaEntity.MariaParameter(strOperator="=", objValue=strEDINo, enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listInvalidRPT01 = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listInvalidRPT01
def getWeekNumList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT WEEK_NUM, WEEK_FIRST_DATE " + \
"FROM DMMDL.DC0024_WEEK_NUM " + \
"WHERE CONVERT(WEEK_NUM, INT)>=(CONVERT(LEFT({DATA_DATE}, 4), INT) - 1)*100+49 " + \
"ORDER BY WEEK_NUM DESC; "
dictMariaParameter = {
"DATA_DATE": MariaEntity.MariaParameter(objValue=strDataDate, enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listWeekNum = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listWeekNum
def getFlow(strEDIConnection, strEDIDB, strEDINo):
strSQL = "SELECT * FROM DWWANT.EDI_FLOW WHERE {EDI_NO}; "
dictMariaParameter = {
"EDI_NO":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strEDINo,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listFlows = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listFlows[0]
def getPOSRPT01SystemSKUList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM, KA_SYSTEM_ACT_NM, POS_WEEK_NUM " + \
" , POS_SKU " + \
"FROM DMMDL.DC0024_POS_RPT01_SYSTEM " + \
"WHERE CONVERT(POS_WEEK_NUM, INT)>=(CONVERT(LEFT({DATA_DATE}, 4), INT) - 1)*100+49 " + \
"ORDER BY KA_SYSTEM_CODE, POS_WEEK_NUM DESC; "
dictMariaParameter = {
"DATA_DATE": MariaEntity.MariaParameter(objValue=strDataDate, enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listPOSRPT01SystemSKU = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listPOSRPT01SystemSKU
def updateJobEndData(strEDIConnection, strEDIDB, strEDINo, strJobID,
enumJobResult):
strSQL = "UPDATE DWWANT.EDI_JOB SET " + \
" {STATUS_ID}, {RESULT_ID}, {DT_END} " + \
" , {UPD_USER_ID}, UPD_DT=NOW() " + \
"WHERE {EDI_NO} AND {EDI_JOB_ID}; "
enumEDIResultID = EDIEnum.EnumEDIResultID.F
if enumJobResult == EDIEnum.EnumJobResult.Success:
enumEDIResultID = EDIEnum.EnumEDIResultID.S
elif enumJobResult == EDIEnum.EnumJobResult.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),
"EDI_JOB_ID":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strJobID,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
return DBEntity.executeMariaData(strEDIConnection, strEDIDB, strSQL)
def getPOSRPT01ProdH1PCSList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM, KA_SYSTEM_ACT_NM " + \
" , SALES_COM_ID_SA, SALES_COM_ABR_SA " + \
" , PROD_H1_ID, PROD_H1_NM, POS_WEEK_NUM " + \
" , ROUND(POS_QTY_PCS, 0) AS POS_QTY_PCS " + \
"FROM DMMDL.DC0024_POS_RPT01_PRODH1 " + \
"WHERE CONVERT(POS_WEEK_NUM, INT)>=(CONVERT(LEFT({DATA_DATE}, 4), INT) - 1)*100+49 " + \
"ORDER BY KA_SYSTEM_CODE, SALES_COM_ID_SA, PROD_H1_ID, POS_WEEK_NUM DESC; "
dictMariaParameter = {
"DATA_DATE": MariaEntity.MariaParameter(objValue=strDataDate, enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listPOSRPT01ProdH1PCS = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listPOSRPT01ProdH1PCS
def getWorkDayT(strEDIConnection, strEDIDB, strDataDate, strTODAY):
strSQL = "SELECT WORKDAY_YM, WORKDAY_YMD, WORKDAY_YN " + \
"FROM DWWANT.CM_WORKDAY " + \
"WHERE {WORKDAY_YMD}; "
dictMariaParameter = {
"WORKDAY_YMD":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strTODAY,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listWorkDayT = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listWorkDayT
def insertNewJob(strEDIConnection, strEDIDB, strEDINo, strJobID, strJobType,
strObjectName, strDependOn):
strSQL = "INSERT INTO DWWANT.EDI_JOB VALUES ( " + \
" {EDI_NO}, {EDI_JOB_ID}, {EDI_JOB_TYPE}, {EDI_OBJ_NM}, {EDI_DEP_JOB_ID} " + \
" , {STATUS_ID}, NULL " + \
" , {DT_BEGIN}, NULL, NULL, NULL, NULL " + \
" , {UPD_USER_ID}, NOW() " + \
"); "
dictMariaParameter = {
"EDI_NO":
MariaEntity.MariaParameter(
objValue=strEDINo,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_JOB_ID":
MariaEntity.MariaParameter(
objValue=strJobID,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_JOB_TYPE":
MariaEntity.MariaParameter(
objValue=strJobType,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_OBJ_NM":
MariaEntity.MariaParameter(
objValue=strObjectName,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_DEP_JOB_ID":
MariaEntity.MariaParameter(
objValue=strDependOn,
enumType=MariaEntity.EnumMariaParameterType.Char),
"STATUS_ID":
MariaEntity.MariaParameter(
objValue=EDIEnum.EnumEDIStatusID.B.name,
enumType=MariaEntity.EnumMariaParameterType.Char),
"DT_BEGIN":
MariaEntity.MariaParameter(
objValue=datetime.datetime.now(),
enumType=MariaEntity.EnumMariaParameterType.DateTime),
"UPD_USER_ID":
MariaEntity.MariaParameter(
objValue="EDIService",
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
return DBEntity.executeMariaData(strEDIConnection, strEDIDB, strSQL)
def getReqList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT REQ_YM, WANT_CHAN_NM, PROD_H1_NM, WANT_COM_NM, PROD_H2_NM, PROD_H3_NM " + \
" , (REQ_AMT/1000) AS REQ_AMT " + \
"FROM DMMDL.DC0021_REQ " + \
"WHERE {DATA_DATE} " + \
"ORDER BY REQ_YM, WANT_CHAN_NM, PROD_H1_NM, WANT_COM_NM, PROD_H2_NM, PROD_H3_NM; "
dictMariaParameter = {
"DATA_DATE":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strDataDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listReq = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listReq
def getMariaDBImportDataCount(strJobConnection, strJobDB, strJobObjectName):
strSQL = "SELECT COUNT(1) AS CN " + \
"FROM {DB_NAME}.{TABLE_NAME}; "
dictMariaParameter = {
"DB_NAME":
MariaEntity.MariaParameter(
objValue=strJobDB,
enumType=MariaEntity.EnumMariaParameterType.Object),
"TABLE_NAME":
MariaEntity.MariaParameter(
objValue=strJobObjectName,
enumType=MariaEntity.EnumMariaParameterType.Object),
}
strSQL = DBEntity.getMariaSQLCommand(strSQL, dictMariaParameter)
listCN = DBEntity.getMariaDataList(strJobConnection, strJobDB, strSQL)
return listCN[0]["CN"]
def getStoreMatlDSystemList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT KA_SYSTEM_CODE, MAX(KA_SYSTEM_NM) AS KA_SYSTEM_NM, MAX(POS_INV_DATE) AS POS_INV_DATE " + \
"FROM DMMDL.DC0024_STORE_MATL_D " + \
"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)
listStoreMatlDSystem = DBEntity.getMariaDataList(strEDIConnection,
strEDIDB, strSQL)
return listStoreMatlDSystem
def updateFlowBeginData(strEDIConnection, strEDIDB, strEDINo, strEDIDate,
strEDITime):
strSQL = "UPDATE DWWANT.EDI_FLOW SET " + \
" {EDI_DATE}, {EDI_TIME} " + \
" , {STATUS_ID}, {DT_BEGIN} " + \
" , {UPD_USER_ID}, UPD_DT=NOW() " + \
"WHERE {EDI_NO}; "
dictMariaParameter = {
"EDI_DATE":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strEDIDate,
enumType=MariaEntity.EnumMariaParameterType.Char),
"EDI_TIME":
MariaEntity.MariaParameter(
strOperator="=",
objValue=strEDITime,
enumType=MariaEntity.EnumMariaParameterType.Char),
"STATUS_ID":
MariaEntity.MariaParameter(
strOperator="=",
objValue=EDIEnum.EnumEDIStatusID.B.name,
enumType=MariaEntity.EnumMariaParameterType.Char),
"DT_BEGIN":
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 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 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 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 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 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 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
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