atpquery.cpp

来自「一个以前收集的基于C/S架构的ERP客户端源代码」· C++ 代码 · 共 581 行 · 第 1/2 页

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "ATPQuery.h"
#include "KSSaleFuc.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "KSAdvReport"
#pragma link "QueryBaseForm"
#pragma link "SDGrid"
#pragma link "fpanel"
#pragma link "SDEdit"
#pragma resource "*.dfm"
TfrmATPQuery *frmATPQuery;
//---------------------------------------------------------------------------
__fastcall TfrmATPQuery::TfrmATPQuery(TComponent* Owner, HWND chWnd, AnsiString MidCode,AnsiString WhereStr)
        :TQueryBaseForm(Owner,chWnd,MidCode,WhereStr)
{
 FilterStr=WhereStr;
 sSQL="";
 QueryGrid=sgATPCloth;
 iMidCode=MidCode;

       FillGridWithData();
       FillPurchaseGridWithData();
       FillSaleGridWithData();    
       FillAnswerDateGridWithData();
       FillAnswerQtyGridWithData();
}
//---------------------------------------------------------------------------
void __fastcall TfrmATPQuery::ClearControl(bool BringToNext)
{
 return;
}
//---------------------------------------------------------------------------
void __fastcall TfrmATPQuery::GetDataFromComObject()
{
 return;
}
//---------------------------------------------------------------------------
void __fastcall TfrmATPQuery::WaitUserInput()
{
 return;
}
//---------------------------------------------------------------------------
void __fastcall TfrmATPQuery::RefreshGridData(int mAction)
{
 return;
}
//---------------------------------------------------------------------------
void __fastcall TfrmATPQuery::DisplayBill()
{
 return;
}
//---------------------------------------------------------------------------
void __fastcall TfrmATPQuery::GetBill(AnsiString BillID)
{
 return;
}
//---------------------------------------------------------------------------
//             sale
AnsiString __fastcall TfrmATPQuery::GetSaleQueryStr()
{
 AnsiString Selectstr,Fromstr,Wherestr, Orderstr;
 Selectstr =""

  "SELECT SohCode, SodGoods, SodCloth,                                           "
  "ClothName =                                                                   "
  "ISNULL((SELECT GoodsName FROM sdGoods WHERE GoodsCode = SodCloth),''),        "
  "SodDesign, SodSize, SodQty ,                                                  "
  "needQty= (select CASE WHEN count(-1) >1 THEN  (                               "
  "      select UnitAbility = max(DeplQty) from ksdepl                           "
  "        where DeplPmpc='1' and DeplGoods = SodCloth and DeplDesign =SodDesign)"
  "    WHEN count(-1) = 1 THEN  (select UnitAbility = max(DeplQty) from ksdepl   "
  "        where DeplPmpc='1' and DeplGoods = SodCloth )                         "
  "    ELSE  '-1'  END from ksdepl                                               "
  "        where DeplPmpc = '1' and DeplGoods = SodCloth)                        "
  "FROM sdSod                                                                    "
  "FULL JOIN sdSoh ON SohCode = SodCode                                          "
  "WHERE SohCheck = '1' AND                                                      "
  "SohSysdate = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 120), 1, 10)           ";
        
 sSQL=Selectstr+Wherestr+Orderstr;
 return(sSQL);
}

//---------------------------------------------------------------------------
//           采购           
AnsiString __fastcall TfrmATPQuery::GetPurchaseQueryStr()
{
 AnsiString Selectstr, wherestr,ClothCode;
 ClothCode = FilterStr.SubString(FilterStr.Pos("SohCode")+9,FilterStr.Length() - FilterStr.Pos("SohCode")+9);
    Selectstr = 
"SELECT  SodCloth,"
"	Paot =(SELECT SUM(GoodsPaot) FROM sdGoods WHERE GoodsCode = SodCloth),                  "
"  	PgdPoc = (SELECT PgdPoc = SUM(PgdPoc) FROM sdPgd WHERE PgdGoodsCode = SodCloth),        "
"	RpodQty = (SELECT SUM(RpodQty) FROM sdRpod FULL JOIN sdRpoh ON RpodCode=RpohCode        "
"		WHERE (RpohCode IS NOT NULL) AND (RpohCheck = '1') AND RpodGoodsCode = SodCloth AND "
"    	(RpodRecDate >= SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 120), 1, 10))),           "
"  	PodQty = (SELECT SUM(PodQty) FROM sdPod  FULL JOIN sdPoh ON PodCode=PohCode             "
"		WHERE (PohCode IS NOT NULL) AND (PohCheck = '1') AND PodGoods = SodCloth AND        "
"    	(PodSendDate >= SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 120), 1, 10))),           "
"  	ZtdQty = (SELECT SUM(ZtdQty) FROM sdZth FULL JOIN sdZtd ON ZthCode = ZtdCode            "
"		WHERE (ZthCode IS NOT NULL) AND (ZthCheck = '1') AND ZtdGoods = SodCloth AND        "
"		(ZtdSendDate >= SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 120), 1, 10)))            "
" FROM sdSod WHERE (SoDCode IS NOT NULL) ";

if ( ClothCode!= "" )
  wherestr = "AND SodCode = "+ClothCode;

 sSQL=Selectstr+wherestr;
 return(sSQL);
}
//---------------------------------------------------------------------------
//
AnsiString __fastcall TfrmATPQuery::GetQueryStr()
{
 AnsiString Selectstr,Fromstr,Wherestr, Orderstr;
         Selectstr =""
"SELECT SohCode, SodGoods, SodCloth,                                               "
"ClothName = ISNULL((SELECT GoodsName FROM sdGoods WHERE GoodsCode = SodCloth),''), " 
"needQty= (select CASE WHEN count(-1) >1 THEN  (                                    "
"	select UnitAbility = max(DeplQty) from ksdepl                                   "
"	  where DeplPmpc='1' and DeplGoods = SodCloth and DeplDesign =SodDesign)        "
"	WHEN count(-1) = 1 THEN  (select UnitAbility = max(DeplQty) from ksdepl         "
"	   where DeplPmpc='1' and DeplGoods = SodCloth )                                "
"	ELSE  '-1'  END from ksdepl                                                     "
"where DeplPmpc = '1' and DeplGoods = SodCloth                                      "
"), StockFopQty, UnSD = StockFopQty-StockDispensAbility, StockDispensAbility,       "
"SodDesign, SodSize, SodQty FROM sdSod                                              "
"FULL JOIN sdSoh   ON SohCode		= SodCode                                       "
"FULL JOIN sdStock ON StockGoodsCode = SodCloth                                     "
"WHERE (SohCode IS NOT NULL) AND (SodGoods IS NOT NULL)                             ";

  //   " WHERE StockFmonth = '200304' AND SohCode = 'XSDD2003041463' ";
 if(FilterStr!="")                              
   Wherestr = " AND "+FilterStr ; 

 sSQL=Selectstr+Wherestr;
 return(sSQL);
}
//---------------------------------------------------------------------------
//      AnswerDate      -- sql
AnsiString __fastcall TfrmATPQuery::GetAnswerDateQueryStr()
{
 AnsiString Selectstr, ClothCode, wherestr;
 ClothCode = FilterStr.SubString(FilterStr.Pos("SohCode")+9,FilterStr.Length() - FilterStr.Pos("SohCode")+9);


         Selectstr =
"SELECT AnswerDate = SUBSTRING(CONVERT(VARCHAR,DATEADD(DAY, SUM(SodQty)/  "
"       (SELECT  SUM(WoQty)/SUM(DATEDIFF( DAY, WoSDate ,WoRDate ))                   "
"       FROM sdWeph                                                                  "
"       FULL JOIN sdWepd ON WephCode = WepdCode                                      "
"       FULL JOIN sdWo   ON WoCode   = WepdWo                                        "
"       FULL JOIN sdGoods ON GoodsCode = WoGoods                                     "
"       WHERE ( WoCode IS NOT NULL ) AND ( WephCode IS NOT NULL)                     "
"       AND (DATEDIFF(Month, WoSDate, GETDATE())<                                    "
"       (SELECT PARAMETERVALUE FROM SDPARAMETER WHERE PARAMETERCODE = '10416')) )    "
"     ,GETDATE()),120) ,1 ,10 )                                                           "
"FROM sdSoh FULL JOIN sdSod ON SohCode = SodCode                                          "
"WHERE (SohCode IS NOT NULL) AND (SohCheck =1) " ;


if ( ClothCode!= "" )
  wherestr = " AND SohCode = "+ClothCode;

 return ( Selectstr + wherestr );
}
//---------------------------------------------------------------------------
//      Max Soh in (arae time ) qty     -- sql
AnsiString __fastcall TfrmATPQuery::GetMaxSohQtyQueryStr()
{
 AnsiString Selectstr;

         Selectstr = 
           "SELECT TOP 1 maxv = SUM(SodQty) FROM sdSoh   "
        "FULL JOIN sdSod ON SohCode = SodCode            "
        "WHERE (SohCode IS NOT NULL )                    "
        "AND (SohCheck = '1')                            "
        "AND (DATEDIFF(Month, SohDate, GETDATE())<       "
        "	(SELECT PARAMETERVALUE FROM SDPARAMETER 	 "
        "				WHERE PARAMETERCODE = '10416')   "
        ") AND (DATEDIFF(Month, SohDate, GETDATE())>=0)  " 
        "GROUP BY sohdate ORDER BY maxv DESC ";
           
 return(Selectstr);
}
//---------------------------------------------------------------------------
//      Min Soh in (arae time ) qty     -- sql
AnsiString __fastcall TfrmATPQuery::GetMinSohQtyQueryStr()
{
 AnsiString Selectstr;

         Selectstr = 
           "SELECT TOP 1 minv = SUM(SodQty) FROM sdSoh   "
        "FULL JOIN sdSod ON SohCode = SodCode            "
        "WHERE (SohCode IS NOT NULL )                    "
        "AND (SohCheck = '1')                            "
        "AND (DATEDIFF(Month, SohDate, GETDATE())<       "
        "	(SELECT PARAMETERVALUE FROM SDPARAMETER 	 "
        "				WHERE PARAMETERCODE = '10416')   "
        ") AND (DATEDIFF(Month, SohDate, GETDATE())>=0)  "
        "GROUP BY sohdate ORDER BY minv ASC ";
           
 return(Selectstr);
}
//---------------------------------------------------------------------------
//      Avg Soh in (arae time ) qty     -- sql
AnsiString __fastcall TfrmATPQuery::GetAvgSohQtyQueryStr()
{
 AnsiString Selectstr;

         Selectstr = 
        "SELECT avgv = SUM(SodQty)/(SELECT COUNT(1) FROM sdSoh ) FROM sdSoh "
        "FULL JOIN sdSod ON SohCode = SodCode            "
        "WHERE (SohCode IS NOT NULL )                    "
        "AND (SohCheck = '1')                            "
        "AND (DATEDIFF(Month, SohDate, GETDATE())<       "
        "	(SELECT PARAMETERVALUE FROM SDPARAMETER 	 "
        "				WHERE PARAMETERCODE = '10416')   "
        ") AND (DATEDIFF(Month, SohDate, GETDATE())>=0)  ";
         
 return(Selectstr);
}
//---------------------------------------------------------------------------
//    DATE  Avg Soh in (arae time ) qty     -- sql
AnsiString __fastcall TfrmATPQuery::GetDateAvgSohQtyQueryStr()
{
 AnsiString Selectstr;

         Selectstr = 
        "SELECT Davgv = SUM(SodQty)/                                              "
        "	DATEDIFF(DAY,(SELECT TOP 1 SohDate FROM sdSoh ORDER BY SohDate ASC),  "
        "				(SELECT TOP 1 SohDate FROM sdSoh ORDER BY SohDate  DESC)) "
        " FROM sdSoh FULL JOIN sdSod ON SohCode = SodCode                         "
        "WHERE (SohCode IS NOT NULL ) AND (SohCheck = '1')                        "
        "AND (DATEDIFF(Month, SohDate, GETDATE())<                                "
        "	(SELECT PARAMETERVALUE FROM SDPARAMETER WHERE PARAMETERCODE = '10416')"
        " ) AND (DATEDIFF(Month, SohDate, GETDATE())>=0)                          " ;
       
 return(Selectstr);
}
//---------------------------------------------------------------------------
//    seRecDateAvgSohQty DATE  Avg Soh in (arae time ) qty     -- sql
AnsiString __fastcall TfrmATPQuery::GetRecDateAvgSohQtyQueryStr()
{
 AnsiString Selectstr;

     Selectstr = 
        "SELECT DRavgv = SUM(DATEDIFF(day,SohDate,SodRecDate)) FROM sdSoh   "
        "FULL JOIN sdSod ON SohCode = SodCode                               "
        "WHERE (SohCode IS NOT NULL )                                       "
        "AND (SohCheck = '1')                                               "
        "AND (DATEDIFF(Month, SohDate, GETDATE())<                          "
        "	(SELECT PARAMETERVALUE FROM SDPARAMETER 	                    "
        "				WHERE PARAMETERCODE = '10416')                      "
        ") AND (DATEDIFF(Month, SohDate, GETDATE())>=0)                     " ;
         
 return(Selectstr);
}

//------Fill AnswerDate GridWithData();-----------------------------------------
//----------------------------

//       日平均计算        %%%%%%%%%%%%%%%%%%%%%%%%%%%
 //---------------------------------------------------------------------------
//      Max Soh in (arae time ) qty     -- sql
AnsiString __fastcall TfrmATPQuery::GetDGMaxSohQtyQueryStr()
{
 AnsiString Selectstr;

         Selectstr = 
           "SELECT TOP 1 maxv = SUM(SodQty) FROM sdSoh   "
        "FULL JOIN sdSod ON SohCode = SodCode            "
        "WHERE (SohCode IS NOT NULL )                    "
        "AND (SohCheck = '1')                            "
        "AND (DATEDIFF(Month, SohDate, GETDATE())<       "
        "	(SELECT PARAMETERVALUE FROM SDPARAMETER 	 "
        "				WHERE PARAMETERCODE = '10416')   "
        ") AND (DATEDIFF(Month, SohDate, GETDATE())>=0)  "
        "GROUP BY sohCode ORDER BY maxv DESC ";


 return(Selectstr);
}
//---------------------------------------------------------------------------
//      Min Soh in (arae time ) qty     -- sql
AnsiString __fastcall TfrmATPQuery::GetDGMinSohQtyQueryStr()