[code delphi]
procedure TFPRDS20._GetProdDay; var xSQL:String; xCount:Integer; begin xCount:=0; xProdD:=''; xSQLFName:=''; try DM1.ADOCommand_UpdateSQL.CommandText:=' if (select OBJECT_ID(''tempdb..#Temp_ProdDay'')) IS NOT NULL Drop Table #Temp_ProdDay ' ' select convert(varchar(11),dateadd(dd,number,' QuotedStr(Query_Search.FieldByName('ProdDayS').AsString) '),112) as ProdDay ' ' into #Temp_ProdDay ' ' from master..spt_values ' ' where type=''p'' and ' ' number < datediff(dd,' QuotedStr(Query_Search.FieldByName('ProdDayS').AsString) ',' QuotedStr(Query_Search.FieldByName('ProdDayE').AsString) ') 1'; DM1.ADOCommand_UpdateSQL.Execute; xSQL:=' select ProdDay,''D_'' Convert(varchar(11),(ProdDay),112) as TrProdDay from #Temp_ProdDay '; DM1._Query_table(DM1.Qtmp,xSQL); if not DM1.Qtmp.IsEmpty then begin DM1.Qtmp.First; While not DM1.Qtmp.Eof do begin xCount:=xCount 1; Case xCount of 1:begin xProdD :=('[' DM1.Qtmp.FieldByName('TrProdDay').AsString ']'); xSQLFName:='isnull(D.[' DM1.Qtmp.FieldByName('TrProdDay').AsString '],' '0' ') as ''' DM1.Qtmp.FieldByName('ProdDay').AsString ''''; xSubFName:='isnull(D.[' DM1.Qtmp.FieldByName('TrProdDay').AsString '],' '0' ') '; end else begin xProdD :=xProdD ',' ('[' DM1.Qtmp.FieldByName('TrProdDay').AsString ']'); xSQLFName:=xSQLFName ',' 'isnull(D.[' DM1.Qtmp.FieldByName('TrProdDay').AsString '],' '0' ') as ''' DM1.Qtmp.FieldByName('ProdDay').AsString ''''; xSubFName:=xSubFName ' ' 'isnull(D.[' DM1.Qtmp.FieldByName('TrProdDay').AsString '],' '0' ') '; end; end; DM1.Qtmp.Next; end; end else begin FunU._ShowMes('捉取不到生產天數 '); Abort; end; except FunU._ShowMes('匯整生產天數失敗，請再重新查詢一次。 '); Abort; end; end; procedure TFPRDS20.Query_SearchAfterScroll(DataSet: TDataSet); var xSQL:String; i:integer; xProdNo:String; begin inherited; if DBGrid_D.Hint = 'UnCheck' then Exit; if Query_Search.FieldByName('IsDelete').AsString='1' then Exit; //**先串起所需天數的語法 _GetProdDay; //**刪除已建立欄位 while DBGrid_D.Columns.Count > 8 do DBGrid_D.Columns.Delete(DBGrid_D.Columns.Count-1); try FPRDS20.Enabled := False; Query_D.DisableControls; try DM1.ADOConnection_InnovaDB.BeginTrans; xSQL:='-- 先crate一個早、晚班、小計的暫存表 ' #13 ' if (select OBJECT_ID(''tempdb..#Temp_DS'')) IS NOT NULL Drop Table #Temp_DS ' #13 ' CREATE TABLE #Temp_DS( [ShiftT] [nVarchar](6) NOT NULL, ' #13 ' [ShiftTNa] [nVarchar](6) NOT NULL ) ' #13 ' insert into #Temp_DS values(''D'',' QuotedStr(A2UToBIG5(ChangeStr('早班'))) ') ' #13 ' insert into #Temp_DS values(''S'',' QuotedStr(A2UToBIG5(ChangeStr('晚班'))) ') ' #13 ' insert into #Temp_DS values(''SumDay'',' QuotedStr(A2UToBIG5(ChangeStr('小計'))) ') ' #13 #13 ' --先匯整早、晚班RM、RD資料利用union all的方式把小計納進來 ' #13 ' if (select OBJECT_ID(''tempdb..#PRD_SumPQty'')) IS NOT NULL Drop Table #PRD_SumPQty ' #13 ' select M.CorpNo,M.ShiftT,D.MPSPCTNo,D.ProdNo, ' #13 ' ''D_'' Convert(varchar(11),(M.ProduceD),112) as ProduceD,SUM(D.PQty-D.TotBQty) as PQty ' #13 ' into #PRD_SumPQty ' #13 ' from RM M,RD D ' #13 ' where M.CorpNo=D.CorpNo ' #13 ' and M.CHNo=D.CHNo ' #13 ' and M.IsDelete<>''1'' ' #13 ' and M.CorpNo=' QuotedStr(Query_Search.fieldByName('CorpNo').AsString) #13 ' and M.PType=' QuotedStr(LeftStr(ComboBox_PType.Text,1)) #13 ' and isnull(D.MPSPCTNo,'''')=' QuotedStr(Query_Search.fieldByName('PCTNo').AsString) #13 ' group by M.CorpNo,M.ShiftT,D.MPSPCTNo,D.ProdNo,M.ProduceD ' #13 ' union all ' #13 ' select M.CorpNo,''SumDay'' as ShiftT,D.MPSPCTNo,D.ProdNo, ' #13 ' ''D_'' Convert(varchar(11),(M.ProduceD),112) as ProduceD,SUM(D.PQty-D.TotBQty) as PQty ' #13 ' from RM M,RD D ' #13 ' where M.CorpNo=D.CorpNo ' #13 ' and M.CHNo=D.CHNo ' #13 ' and M.IsDelete<>''1'' ' #13 ' and M.CorpNo=' QuotedStr(Query_Search.fieldByName('CorpNo').AsString) #13 ' and M.PType=' QuotedStr(LeftStr(ComboBox_PType.Text,1)) #13 ' and isnull(D.MPSPCTNo,'''')=' QuotedStr(Query_Search.fieldByName('PCTNo').AsString) #13 ' group by M.CorpNo,D.MPSPCTNo,D.ProdNo,M.ProduceD ' #13 #13 ' --先把班別表和生產數做結合 ' #13 ' if (select OBJECT_ID(''tempdb..#PRD_ShiftT'')) IS NOT NULL Drop Table #PRD_ShiftT ' #13 ' select DS.*,M.CorpNo,M.MPSPCTNo,M.ProdNo,M.ProduceD ' #13 ' into #PRD_ShiftT ' #13 ' from #Temp_DS DS,(select CorpNo,MPSPCTNo,ProdNo,ProduceD from #PRD_SumPQty ' #13 ' group by CorpNo,MPSPCTNo,ProdNo,ProduceD) M ' #13 ' order by M.ProduceD,M.ProdNo,DS.ShiftT ' #13 #13 ' --結合 ' #13 ' if (select OBJECT_ID(''tempdb..#PRD_TD'')) IS NOT NULL Drop Table #PRD_TD ' #13 ' select ST.*,isnull(DSum.PQty,''0'') as DSumQty ' #13 ' into #PRD_TD ' #13 ' from #PRD_ShiftT ST ' #13 ' left join #PRD_SumPQty DSum on ST.ShiftT COLLATE Chinese_PRC_Stroke_CI_AS=DSum.ShiftT COLLATE Chinese_PRC_Stroke_CI_AS and ' #13 ' ST.CorpNo COLLATE Chinese_PRC_Stroke_CI_AS=DSum.CorpNo COLLATE Chinese_PRC_Stroke_CI_AS and ' #13 ' ST.MPSPCTNo COLLATE Chinese_PRC_Stroke_CI_AS=DSum.MPSPCTNo COLLATE Chinese_PRC_Stroke_CI_AS and ' #13 ' ST.ProdNo COLLATE Chinese_PRC_Stroke_CI_AS=DSum.ProdNo COLLATE Chinese_PRC_Stroke_CI_AS and ' #13 ' ST.ProduceD COLLATE Chinese_PRC_Stroke_CI_AS=DSum.ProduceD COLLATE Chinese_PRC_Stroke_CI_AS ' #13 #13 ' --將匯整出來的資料轉成橫向 ' #13 ' if (select OBJECT_ID(''tempdb..#PRD_PIVOTQty'')) IS NOT NULL Drop Table #PRD_PIVOTQty ' #13 ' select * into #PRD_PIVOTQty ' #13 ' from (select * from #PRD_TD) as xx ' #13 ' PIVOT ' #13 ' (sum(DSumQty) ' #13 ' for ProduceD ' #13 ' in( ' xProdD ' )) as xxx '; //FunU._WriteWideString2File(FunU._GetDesktopDir 'FPRDS20_1.txt',xSQL); DM1.ADOCommand_UpdateSQL.CommandText :=xSQL; DM1.ADOCommand_UpdateSQL.Execute; DM1.ADOConnection_InnovaDB.CommitTrans; except DM1.ADOConnection_InnovaDB.RollbackTrans; FunU._ShowMes(' 資料匯總錯誤，請再重試一次'); Abort; end; //**try except Finally //**--將生產數最終結果與生產管制表結合 DM1.SQL_Command:=' if (select OBJECT_ID(''tempdb..#PRD_Finaly'')) IS NOT NULL Drop Table #PRD_Finaly ' #13 ' select M.PCTNo,M.ProdNo,P.PnameD ' #13 ' ,Convert(varchar(10),M.DNeedQty) as DNeedQty,Convert(varchar(10),isnull(M.BondedQty,0)) as BondedQty ' #13 ' ,ISNULL(M.CusNa,'''') as CusNa,ISNULL(M.Rem,'''') as Rem,isnull(M.ShipRem,'''') as ShipRem,isnull(D.ShiftTNa,'''') as ShiftT,' xSQLFName #13 ' ,( ' xSubFName ' ) as SumPQty ' #13 ' into #PRD_Finaly ' #13 ' from PCTD M ' #13 ' left join #PRD_PIVOTQty D on M.CorpNo=D.CorpNo and M.PCTNo=D.MPSPCTNo and M.ProdNo=D.ProdNo ' #13 ' ,Prod P ' #13 ' where M.ProdNo=P.ProdNo ' #13 ' and M.PCTNo=' QuotedStr(Query_Search.fieldByName('PCTNo').AsString) #13 ' order by M.ProdNo'; //FunU._WriteWideString2File(FunU._GetDesktopDir 'FPRDS20_2.txt',DM1.SQL_Command); DM1.ADOCommand_UpdateSQL.CommandText:=DM1.SQL_Command; DM1.ADOCommand_UpdateSQL.Execute; DM1.SQL_Command:='select * from #PRD_Finaly order by ProdNo '; DM1._Query_table(Query_D,DM1.SQL_Command); //**設定DBGrid_D顯示格 式 for i:=0 to Query_D.FieldCount-1 do begin if ( (Query_D.Fields[i].FieldName = 'PCTNo') or (Query_D.Fields[i].FieldName = 'ProdNo') or (Query_D.Fields[i].FieldName = 'PnameD') or (Query_D.Fields[i].FieldName = 'DNeedQty') or (Query_D.Fields[i].FieldName = 'BondedQty') or (Query_D.Fields[i].FieldName = 'CusNa') or (Query_D.Fields[i].FieldName = 'Rem') or (Query_D.Fields[i].FieldName = 'ShipRem') or (Query_D.Fields[i].FieldName = 'ShiftT') ) then Continue; DBGrid_D.Columns.Add; DBGrid_D.Columns[DBGrid_D.Columns.Count-1].FieldName := Query_D.Fields[i].FieldName; if Query_D.Fields[i].FieldName = 'SumPQty' then DBGrid_D.Columns[DBGrid_D.Columns.Count-1].Title.Caption := ChangeStr('合計 ') else DBGrid_D.Columns[DBGrid_D.Columns.Count-1].Title.Caption := Query_D.Fields[i].FieldName; end; //**設定DBGrid_D 欄位顯示否 xProdNo:=''; Query_D.First; While not Query_D.Eof do begin if xProdNo=Query_D.FieldByName('ProdNo').AsString then begin Query_D.Edit; Query_D.FieldByName('ProdNo').AsString:=' '; Query_D.FieldByName('PnameD').AsString:=' '; Query_D.FieldByName('DNeedQty').AsString:=' '; Query_D.FieldByName('BondedQty').AsString:=' '; Query_D.FieldByName('CusNa').AsString:=' '; Query_D.FieldByName('Rem').AsString:=' '; Query_D.FieldByName('ShipRem').AsString:=' '; end else xProdNo:=Query_D.FieldByName('ProdNo').AsString; Query_D.Next; end; Query_D.First; Query_D.EnableControls; FPRDS20.Enabled := True; end; //**try Finally end;
[/code]
各位大大 小弟把這一段的程式碼貼出來供各位大大參考 小弟用的方法很笨，請各位大大不要見笑^^ 程式碼太亂的話，請見諒!!!!
發表時間：2011-01-18 16:25:12
IP:59.125.xxx.xxx 訂閱
