{"id":2374,"date":"2024-12-13T17:25:07","date_gmt":"2024-12-13T09:25:07","guid":{"rendered":"http:\/\/www.jh1999.cn\/?p=2374"},"modified":"2024-12-13T17:25:08","modified_gmt":"2024-12-13T09:25:08","slug":"delphi%e6%8c%89%e6%9c%88%e4%bb%bd%e7%bb%9f%e8%ae%a1","status":"publish","type":"post","link":"http:\/\/www.jh1999.cn\/?p=2374","title":{"rendered":"Delphi\u6309\u6708\u4efd\u7edf\u8ba1"},"content":{"rendered":"<div class=\"gb-container gb-container-b3abba47\">\n\n<p class=\"wp-block-paragraph\">\u7528Delphi\u5f00\u53d1\u7684\u6570\u636e\u5e93\u5e94\u7528\u8f6f\u4ef6\uff0c\u6309\u6708\u4efd\u7edf\u8ba1\u751f\u6210\u62a5\u8868\uff0c\u662f\u5f88\u5e38\u89c1\u7684\u9700\u6c42\u3002\u4f46\u5982\u679c\u8981\u6c42\u6309\u4ea7\u54c1\u6392\u5217\uff0c\u4ee5\u6708\u4efd\u4f5c\u4e3a\u680f\u6765\u8fdb\u884c\u5c0f\u8ba1\uff0c\u8fd8\u662f\u9700\u8981\u4e00\u70b9\u6280\u5de7\u3002\u4ee5\u4e0b\u662fDelphi + SqlServer\u5f00\u53d1\u73af\u5883\u4e0b\u7684\u4ee3\u7801\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u53d6\u51fa\u6570\u636e\u5b58\u5165\u4e34\u65f6\u8868 tmpDb\uff0c\u628a\u9500\u552e\u65e5\u671f\u8f6c\u6362\u6210YYYYMM\u7684\u5e74\u6708\u683c\u5f0f\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>procedure TfmaxSaleTrend.btnEnqFGClick(Sender: TObject);\nvar\n  tmpDb,tmpEnq:String;\nbegin\n  inherited;\n  screen.Cursor:=crSqlWait;\n  tmpDb:='#jhProd_x53_1'+sys_usid;\n  sSql:='if object_id(N''&#91;Tempdb]..&#91;'+tmpDb+']'') is not null  Drop table '+tmpDb;\n  q01.Close;\n  q01.SQL.Text:=sSql;\n  q01.ExecSQL;\n  sDay1:=FormatDatetime('YYYYMMDD',dtpFrom.Date);\n  sDay2:=FormatDatetime('YYYYMMDD',dtpTo.Date);\n  begin\n    sSql:='Select ItemId,OtsYM,sum(SalesQty) as SalesQty into '+tmpDb\n      +' from ('\n      +' select ItemId,Year(ConfirmedDlv)*100+Month(ConfirmedDlv)as OtsYM,SalesQty '\n      +' from SalesLine d1 '\n      +' where SalesStatus &lt; 4 '\n      +' and ConfirmedDlv >='+''''+sDay1+''''+' and ConfirmedDlv &lt;='+''''+sDay2+''''\n      +' and exists(select * from SalesTable d2 where d1.SalesId=d2.SalesId '\n      +' and d2.SalesPoolId in (''GEN'',''SPO'',''C2C'',''SPC'',''CN'')) '\n      +' and exists(select * from InventTable d3 where d1.ItemId=d3.ItemId '\n      +' and substring(d3.ItemGroupId,1,3)=''PD-'')'\n      +' ) as dA group by ItemId,OtsYM ';\n  end;\n  q01.Close;\n  q01.SQL.Text:=sSql;\n  q01.ExecSQL;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u628a\u4e0a\u8ff0\u4e34\u65f6\u8868\u4e2d\u7684\u6570\u636e\uff0c\u6309\u6708\u4efd\u5c55\u5f00\u518d\u5b58\u5165\u7b2c\u4e8c\u4e2a\u4e34\u65f6\u8868 tmpEnq<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>  tmpEnq:='#jhProd_x53_2'+sys_usid;\n  sSql:='if object_id(N''&#91;Tempdb]..&#91;'+tmpEnq+']'') is not null  Drop table '+tmpEnq;\n  q01.Close;\n  q01.SQL.Text:=sSql;\n  q01.ExecSQL;\n  sSql:='Select d1.ItemId,sum(d1.SalesQty) as SalesSum';\n  qCalc.Close;\n  qCalc.SQL.Text:='select distinct OtsYM from '+tmpDb+' order by OtsYM';\n  qCalc.Open;\n  while not qCalc.Eof do\n  begin\n    sField:=qCalc.FieldByName('OtsYM').AsString;\n    sSql:=sSql+',(select SalesQty from '+tmpDb+' d2 where d2.ItemId=d1.ItemId '\n            +'and d2.OtsYM='+''''+sField+''''+') as '+''''+sField+'''';\n    qCalc.Next;\n  end;\n  sSql:=sSql+' into '+tmpEnq+' from '+tmpDb+' d1 group by d1.ItemId';\n  q01.Close;\n  q01.SQL.Text:=sSql;\n  q01.ExecSQL;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u628a\u7b2c\u4e8c\u4e2a\u4e34\u65f6\u8868 tmpEnq\u7684\u6570\u636e\u4e0e\u57fa\u7840\u8868\u4e2d\u7684\u6570\u636e\uff0c\u5173\u8054\u540e\u53d6\u51fa\u6765\u663e\u793a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>  qProds.Close;\n  if rdgType.ItemIndex = 0 then\n  begin\n    qProds.SQL.Text:='select d2.CustModelNo,d1.* from '\n                  +tmpEnq+' d1,InventTable d2 where d1.ItemId=d2.ItemId';\n  end else\n  begin\n    qProds.SQL.Text:='select * from '+tmpEnq;\n  end;\n  qProds.Open;\n  qProds.Sort:=sField+' DESC';\n  dbGridEh1.Columns&#91;0].Footer.ValueType:=fvtStaticText;\n  dbGridEh1.Columns&#91;0].Footer.Value:='\u5408\u8a08';\n  screen.Cursor:=crDefault;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u4e0a\u8ff0\u6848\u4f8b\u662f\u5728Delphi\u4e2d\u5b9e\u73b0\u7684\uff0c\u5728\u7a0b\u5e8f\u4e2d\u6267\u884c\uff0c\u5982\u679c\u6539\u5230SQL Server\u91cc\uff0c\u6bd4\u4f8b\u5199\u6210\u89c6\u9891\u6216\u9884\u5b58\u5b58\u50a8\u8fc7\u7a0b\uff0c\u6267\u884c\u65f6\u5957\u5165\u53d8\u91cf\uff0c\u5c31\u66f4\u52a0\u7684\u7075\u6d3b\u3002\u89c1\u4e0b\u8ff0 SQL \u8bed\u53e5\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>declare @param1 varchar(10)\ndeclare @param2 varchar(10)\ndeclare @param3 varchar(10)\nset @param1='2018-07-01'\nset @param2='2018-07-15'\nset @param3='fin'\n\nif object_id(N'&#91;tempdb]..&#91;#_jhERPsoDaily]') is not null drop table &#91;#_jhERPsoDaily]\n\nselect d1.itID,d2.TransDate,sum(d1.Qty)as Qty,sum(d1.LineAmount)as Amount\n  into &#91;#_jhERPsoDaily]\n  from soSaleJLine d1,soSaleJTable d2\n  where d1.JournalID=d2.JournalID\n  and d2.TransDate>=@param1 and d2.TransDate&lt;=@param2\n  and d2.Posted=1 and d2.CompName=@param3\n  group by d1.itID,d2.TransDate\n\ndeclare @i       Integer\ndeclare @j       Integer\ndeclare @day\t smalldatetime\ndeclare @strDay  varchar(10)\ndeclare @sql     varchar(8000)\n\nset @j=DateDiff(day,@param1,@param2)\nset @day=@param1\nset @i=0\n\nif(@j > 31)\nbegin\n  set @sql='select ''\u65e5\u671f\u8303\u56f4\u4e0d\u80fd\u8d85\u8fc730\u5929'' as \u8b66\u544a '\nend\nelse\nbegin\nset @sql='Select d1.itID,d2.itName,d2.stUnit,sum(d1.qty)as \u603b\u6570\u91cf,sum(d1.Amount) as AllAmount '\nwhile (@i&lt;=@j)\nbegin\n set @strDay=convert(varchar(10),@day,112)\n set @sql=@sql+',(select qty from &#91;#_jhERPsoDaily] d8 where d8.itID=d1.itID '\n set @sql=@sql+'and d8.TransDate='+''''+@strDay+''''+') as '+''''+@strDay+''+'\u6570\u91cf'' '\n set @sql=@sql+',(select Amount from &#91;#_jhERPsoDaily] d8 where d8.itID=d1.itID '\n set @sql=@sql+'and d8.TransDate='+''''+@strDay+''''+') as '+''''+@strDay+''+'\u91d1\u989d'' '\n set @i=@i+1\n set @day=DateAdd(day,1,@day)\nend\nset @sql=@sql+'from &#91;#_jhERPsoDaily] d1 left join enInvent d2 on d1.itID=d2.itID '\nset @sql=@sql+'group by d1.itID,d2.itName,d2.stUnit'\nend\nexec(@sql)<\/code><\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"934\" src=\"http:\/\/www.jh1999.cn\/wp-content\/uploads\/2024\/12\/DaJieMeiNv.png\" alt=\"\" class=\"wp-image-2453\" style=\"width:277px;height:auto\" srcset=\"http:\/\/www.jh1999.cn\/wp-content\/uploads\/2024\/12\/DaJieMeiNv.png 650w, http:\/\/www.jh1999.cn\/wp-content\/uploads\/2024\/12\/DaJieMeiNv-209x300.png 209w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/figure>\n<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>\u7528Delphi\u5f00\u53d1\u7684\u6570\u636e\u5e93\u5e94\u7528\u8f6f\u4ef6\uff0c\u6309\u6708\u4efd\u7edf\u8ba1\u751f\u6210\u62a5\u8868\uff0c\u662f\u5f88\u5e38\u89c1\u7684\u9700\u6c42\u3002\u4f46\u5982\u679c\u8981\u6c42\u6309\u4ea7\u54c1\u6392\u5217\uff0c\u4ee5\u6708\u4efd\u4f5c\u4e3a\u680f\u6765\u8fdb &#8230; <a title=\"Delphi\u6309\u6708\u4efd\u7edf\u8ba1\" class=\"read-more\" href=\"http:\/\/www.jh1999.cn\/?p=2374\" aria-label=\"Read more about Delphi\u6309\u6708\u4efd\u7edf\u8ba1\">\u9605\u8bfb\u66f4\u591a<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2374","post","type-post","status-publish","format-standard","hentry","category-kfbj"],"_links":{"self":[{"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/posts\/2374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2374"}],"version-history":[{"count":13,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/posts\/2374\/revisions"}],"predecessor-version":[{"id":2454,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/posts\/2374\/revisions\/2454"}],"wp:attachment":[{"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2374"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}