app

‘**************************************************************************************
‘* 功能 ： 入口
‘* 参数 ：
‘* 返回值：
‘* 备注 ：
‘**************************************************************************************
Public Sub Create(ByVal i As Integer)

Dim formatFilePath As String
Dim strFileName As String
Dim rs As ADODB.Recordset
Dim sql, excuteSql, orderSql As String

On Error GoTo ErrHandler

'暂收警告开始
Call WriteLog(Log_Prompt, "zanshou", I_MESSAGE5 & "    " & Now())

' Format文件路径
formatFilePath = g_Path & Template_Dir & zsFileName & File_ExtentionName

'创建excel对象
Set xlApp = CreateObject("excel.application")
'打开template
Set xlBook = xlApp.Workbooks.Open(formatFilePath)
'设置excel对象可见
xlApp.Visible = False
'禁止弹出对话框
xlApp.DisplayAlerts = False

'只需要拼接条件的sql
 sql = "select T3.PRO_PERIOD AS COL1," & _
"T1.POOMS_PO_NUM AS COL2," & _
"T3.SOD_WST_SS_SM_ORDER_NO AS COL3," & _
"T1.ITEM_NUM AS COL4," & _
"T2.PRT_CDESC AS COL5," & _
"T1.DWG_NO AS COL6," & _
"GET_BIANSHU(T1.PD2_PM2_RQST_NO) AS COL7," & _
"T1.QNTY AS COL8," & _
"T1.DEL_DT AS COL9," & _
"NVL(T1.QNTY - T4.SUMZS,T1.QNTY) AS COL10," & _
"GET_delay_days(T1.DEL_DT) AS COL11," & _
"T2.VEN_VENDOR_NUM AS COL12," & _
"T2.BUYER AS COL13 " & _
"FROM T_PODTL T1 " & _
"LEFT JOIN T_POMSTER T2 " & _
"ON T1.POOMS_PO_NUM = T2.PO_NUM " & _
"LEFT JOIN T_PRMSTER T3 " & _
"ON T1.PD2_PM2_RQST_NO = T3.RQST_NO " & _
"LEFT JOIN (SELECT PODTL_POOMS_PO_NUM, NVL(SUM(QTY_RCVD),0) AS SUMZS " & _
"            FROM T_MIDTL " & _
"          GROUP BY PODTL_POOMS_PO_NUM) T4 " & _
"ON T1.POOMS_PO_NUM = T4.PODTL_POOMS_PO_NUM  " & _
"WHERE GET_delay_days(T1.DEL_DT) > 0  AND (T1.QNTY <>T4.SUMZS OR T4.SUMZS IS NULL) "
orderSql = " ORDER BY COL1,COL2"

'⑤------------------------------------------
Set xlSheet = xlBook.Sheets(SheetName5)
xlSheet.Activate

excuteSql = sql & " AND (T3.PRO_PERIOD LIKE '%A1' OR T3.PRO_PERIOD LIKE '%B3' OR T3.PRO_PERIOD LIKE '%C5')"
excuteSql = excuteSql & orderSql
Set rs = m_Conn.Execute(excuteSql)

xlSheet.[a2].CopyFromRecordset rs
xlSheet.Cells(1, 1).Select
xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'④------------------------------------------
Set xlSheet = xlBook.Sheets(SheetName4)
xlSheet.Activate

excuteSql = sql & " AND (T3.PRO_PERIOD LIKE '%A2' OR T3.PRO_PERIOD LIKE '%B4' OR T3.PRO_PERIOD LIKE '%C6') AND T1.POOMS_PO_NUM LIKE 'Z%'"
excuteSql = excuteSql & orderSql
Set rs = m_Conn.Execute(excuteSql)

xlSheet.[a2].CopyFromRecordset rs
xlSheet.Cells(1, 1).Select
xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'③------------------------------------------
Set xlSheet = xlBook.Sheets(SheetName3)
xlSheet.Activate

excuteSql = sql & " AND (T3.PRO_PERIOD LIKE '%A2' OR T3.PRO_PERIOD LIKE '%B4' OR T3.PRO_PERIOD LIKE '%C6') AND T1.POOMS_PO_NUM LIKE 'W%'"
excuteSql = excuteSql & orderSql
Set rs = m_Conn.Execute(excuteSql)

xlSheet.[a2].CopyFromRecordset rs
xlSheet.Cells(1, 1).Select
xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'②------------------------------------------
Set xlSheet = xlBook.Sheets(SheetName2)
xlSheet.Activate

excuteSql = sql & " AND (T3.PRO_PERIOD LIKE '%A2' OR T3.PRO_PERIOD LIKE '%B4' OR T3.PRO_PERIOD LIKE '%C6') AND T1.POOMS_PO_NUM LIKE 'L%'"
excuteSql = excuteSql & orderSql

xlSheet.[a2].CopyFromRecordset rs
xlSheet.Cells(1, 1).Select
xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous


'①------------------------------------------
Set xlSheet = xlBook.Sheets(SheetName1)
xlSheet.Activate

excuteSql = sql & " AND T1.POOMS_PO_NUM LIKE 'P%'"
excuteSql = excuteSql & orderSql

Set rs = m_Conn.Execute(excuteSql)

xlSheet.[a2].CopyFromRecordset rs
xlSheet.Cells(1, 1).Select
xlSheet.Range("A2:M" & (rs.RecordCount + 1)).Borders.LineStyle = xlContinuous

'-------------------------------------------

strFileName = g_Path & Send_Dir & zsFileName & "_" & Format(Now, "YYMMDD") & File_ExtentionName
xlBook.SaveAs strFileName
xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Call WriteLog(Log_Prompt, "zanshou", I_MESSAGE6 & "    " & Now())

Exit Sub

ErrHandler:

xlBook.Close
' 结束EXCEL对象
xlApp.Quit
' 释放xlApp对象
Set xlApp = Nothing
Call WriteLog(Log_Error, "zanshou", Err.Description)

End Sub