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 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 getSystemOrdrYMList(strEDIConnection, strEDIDB, strDataDate):
strSQL = "SELECT SYSTEM_ORDR_YM " + \
"FROM (SELECT DISTINCT LEFT(SYSTEM_ORDR_DATE, 6) AS SYSTEM_ORDR_YM " + \
" FROM DMMDL.DC0024_ORDR_SYS_D) A " + \
"ORDER BY SYSTEM_ORDR_YM DESC; "
listSystemOrdrYM = DBEntity.getMariaDataList(strEDIConnection, strEDIDB,
strSQL)
return listSystemOrdrYM
def getInvalidRPT01WHComInvList(strEDIConnection, strEDIDB):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM " + \
" , CONCAT(KA_SYSTEM_CODE, '-', KA_WH_CODE) AS KA_WH_CODE, KA_WH_NM " + \
" , SALES_COM_WH_CN, SALES_COM_DE_CN " + \
"FROM DMMDL.DC0024_INVALID_RPT01_WH_COM_INV " + \
"ORDER BY KA_SYSTEM_CODE, KA_WH_CODE " + \
"; "
listInvalidRPT01StoreComInv = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listInvalidRPT01StoreComInv
def getInvalidRPT01StoreInvList(strEDIConnection, strEDIDB):
strSQL = "SELECT KA_SYSTEM_CODE, KA_SYSTEM_NM " + \
" , SYSTEM_STORE_CODE, SYSTEM_STORE_NM " + \
" , INV_DATE_MIN, INV_DATE_MAX " + \
"FROM DMMDL.DC0024_INVALID_RPT01_STORE_INV " + \
"ORDER BY KA_SYSTEM_CODE, INV_DATE_MIN, INV_DATE_MAX " + \
"; "
listInvalidRPT01StoreInv = DBEntity.getMariaDataList(strEDIConnection, strEDIDB, strSQL)
return listInvalidRPT01StoreInv
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 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 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 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 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 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