848 lines
46 KiB
ObjectPascal
848 lines
46 KiB
ObjectPascal
unit U_JGPBCKKCList_JSYCXMgg;
|
||
|
||
interface
|
||
|
||
uses
|
||
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
|
||
Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
|
||
cxDataStorage, cxEdit, DB, cxDBData, cxGridCustomTableView,
|
||
cxGridTableView, cxGridBandedTableView, cxGridDBBandedTableView,
|
||
cxGridLevel, cxClasses, cxControls, cxGridCustomView, cxGridDBTableView,
|
||
cxGrid, StdCtrls, ComCtrls, ExtCtrls, ToolWin, cxGridCustomPopupMenu,
|
||
cxGridPopupMenu, ADODB, DBClient, cxDropDownEdit, cxCheckBox, RM_Common,
|
||
RM_Class, RM_e_Xls, RM_Dataset, RM_System, RM_GridReport, Menus,
|
||
cxCalendar, cxButtonEdit, cxTextEdit, cxPC, cxLookAndFeels,
|
||
cxLookAndFeelPainters, cxNavigator, dxBarBuiltInMenu;
|
||
|
||
type
|
||
TfrmJGPBCKKCList_JSYCXMgg = class(TForm)
|
||
ToolBar1: TToolBar;
|
||
TBRafresh: TToolButton;
|
||
TBFind: TToolButton;
|
||
TBClose: TToolButton;
|
||
Panel1: TPanel;
|
||
ADOQueryCmd: TADOQuery;
|
||
ADOQueryMain: TADOQuery;
|
||
ADOQueryTemp: TADOQuery;
|
||
DataSource1: TDataSource;
|
||
cxGridPopupMenu1: TcxGridPopupMenu;
|
||
CDS_Main: TClientDataSet;
|
||
RM1: TRMGridReport;
|
||
RMDBMain: TRMDBDataSet;
|
||
RMXLSExport1: TRMXLSExport;
|
||
RMDBHZ: TRMDBDataSet;
|
||
CDS_HZ: TClientDataSet;
|
||
CDS_PRT: TClientDataSet;
|
||
PopupMenu1: TPopupMenu;
|
||
N1: TMenuItem;
|
||
N2: TMenuItem;
|
||
cxGrid2: TcxGrid;
|
||
Label3: TLabel;
|
||
CheHao: TEdit;
|
||
ToolButton2: TToolButton;
|
||
Label2: TLabel;
|
||
cxStyleRepository1: TcxStyleRepository;
|
||
cxStyle1: TcxStyle;
|
||
cxStyle2: TcxStyle;
|
||
Label1: TLabel;
|
||
BegDate: TDateTimePicker;
|
||
ADOQuery1: TADOQuery;
|
||
cxStyle3: TcxStyle;
|
||
cxGrid2Level1: TcxGridLevel;
|
||
TV1: TcxGridDBBandedTableView;
|
||
V1CheHao: TcxGridDBBandedColumn;
|
||
V1SJName: TcxGridDBBandedColumn;
|
||
cxStyle4: TcxStyle;
|
||
Label4: TLabel;
|
||
SJName: TEdit;
|
||
V1Qty1: TcxGridDBBandedColumn;
|
||
V1Column4: TcxGridDBBandedColumn;
|
||
V1Column5: TcxGridDBBandedColumn;
|
||
V1Qty2: TcxGridDBBandedColumn;
|
||
V1Price2: TcxGridDBBandedColumn;
|
||
V1Money2: TcxGridDBBandedColumn;
|
||
V1Qty3: TcxGridDBBandedColumn;
|
||
V1Price3: TcxGridDBBandedColumn;
|
||
V1Money3: TcxGridDBBandedColumn;
|
||
V1Qty4: TcxGridDBBandedColumn;
|
||
V1Price4: TcxGridDBBandedColumn;
|
||
V1Money4: TcxGridDBBandedColumn;
|
||
V1Qty5: TcxGridDBBandedColumn;
|
||
V1price5: TcxGridDBBandedColumn;
|
||
V1Money5: TcxGridDBBandedColumn;
|
||
V1Qty6: TcxGridDBBandedColumn;
|
||
V1price6: TcxGridDBBandedColumn;
|
||
V1Money6: TcxGridDBBandedColumn;
|
||
V1Qty7: TcxGridDBBandedColumn;
|
||
V1Price7: TcxGridDBBandedColumn;
|
||
V1Money7: TcxGridDBBandedColumn;
|
||
V1Qty8: TcxGridDBBandedColumn;
|
||
V1Price8: TcxGridDBBandedColumn;
|
||
V1Money8: TcxGridDBBandedColumn;
|
||
V1Qty9: TcxGridDBBandedColumn;
|
||
V1price9: TcxGridDBBandedColumn;
|
||
V1Money9: TcxGridDBBandedColumn;
|
||
V1Qty10: TcxGridDBBandedColumn;
|
||
V1Price10: TcxGridDBBandedColumn;
|
||
V1Money10: TcxGridDBBandedColumn;
|
||
V1Qty11: TcxGridDBBandedColumn;
|
||
V1Price11: TcxGridDBBandedColumn;
|
||
V1Money11: TcxGridDBBandedColumn;
|
||
V1Qty12: TcxGridDBBandedColumn;
|
||
V1Price12: TcxGridDBBandedColumn;
|
||
V1Money12: TcxGridDBBandedColumn;
|
||
V1Qty13: TcxGridDBBandedColumn;
|
||
V1Price13: TcxGridDBBandedColumn;
|
||
V1Money13: TcxGridDBBandedColumn;
|
||
V1ZCC: TcxGridDBBandedColumn;
|
||
V1YFMoney: TcxGridDBBandedColumn;
|
||
V1Qty14: TcxGridDBBandedColumn;
|
||
V1Price14: TcxGridDBBandedColumn;
|
||
V1Money14: TcxGridDBBandedColumn;
|
||
V1Qty15: TcxGridDBBandedColumn;
|
||
V1Price15: TcxGridDBBandedColumn;
|
||
V1Money15: TcxGridDBBandedColumn;
|
||
V1Qty16: TcxGridDBBandedColumn;
|
||
V1Price16: TcxGridDBBandedColumn;
|
||
V1Money16: TcxGridDBBandedColumn;
|
||
V1CE: TcxGridDBBandedColumn;
|
||
V1Qty17: TcxGridDBBandedColumn;
|
||
V1Price17: TcxGridDBBandedColumn;
|
||
V1Money17: TcxGridDBBandedColumn;
|
||
V1ZMoney: TcxGridDBBandedColumn;
|
||
V1YHMoney: TcxGridDBBandedColumn;
|
||
V1ZJMoney: TcxGridDBBandedColumn;
|
||
TFHCX: TToolButton;
|
||
TSPCX: TToolButton;
|
||
RadioGroup1: TRadioGroup;
|
||
THDWC: TToolButton;
|
||
TCXHD: TToolButton;
|
||
cxTabControl1: TcxTabControl;
|
||
V1Ssel: TcxGridDBBandedColumn;
|
||
Panel2: TPanel;
|
||
procedure FormDestroy(Sender: TObject);
|
||
procedure FormClose(Sender: TObject; var Action: TCloseAction);
|
||
procedure TBRafreshClick(Sender: TObject);
|
||
procedure ConNoMChange(Sender: TObject);
|
||
procedure TBCloseClick(Sender: TObject);
|
||
procedure FormShow(Sender: TObject);
|
||
procedure TBFindClick(Sender: TObject);
|
||
procedure MPRTCodeNameChange(Sender: TObject);
|
||
procedure N1Click(Sender: TObject);
|
||
procedure N2Click(Sender: TObject);
|
||
procedure SXBatchNOChange(Sender: TObject);
|
||
procedure FactoryNameChange(Sender: TObject);
|
||
procedure Tv1CellDblClick(Sender: TcxCustomGridTableView;
|
||
ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
|
||
AShift: TShiftState; var AHandled: Boolean);
|
||
procedure ToolButton2Click(Sender: TObject);
|
||
procedure RadioGroup1Click(Sender: TObject);
|
||
procedure TFHCXClick(Sender: TObject);
|
||
procedure TSPCXClick(Sender: TObject);
|
||
procedure cxTabControl1Change(Sender: TObject);
|
||
procedure THDWCClick(Sender: TObject);
|
||
procedure TCXHDClick(Sender: TObject);
|
||
procedure V1Qty14CompareRowValuesForCellMerging(Sender: TcxGridColumn;
|
||
ARow1: TcxGridDataRow; AProperties1: TcxCustomEditProperties;
|
||
const AValue1: Variant; ARow2: TcxGridDataRow;
|
||
AProperties2: TcxCustomEditProperties; const AValue2: Variant;
|
||
var AAreEqual: Boolean);
|
||
procedure V1Money14CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
procedure V1Qty18CompareRowValuesForCellMerging(Sender: TcxGridColumn;
|
||
ARow1: TcxGridDataRow; AProperties1: TcxCustomEditProperties;
|
||
const AValue1: Variant; ARow2: TcxGridDataRow;
|
||
AProperties2: TcxCustomEditProperties; const AValue2: Variant;
|
||
var AAreEqual: Boolean);
|
||
procedure V1Money18CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
procedure V1Qty15CompareRowValuesForCellMerging(Sender: TcxGridColumn;
|
||
ARow1: TcxGridDataRow; AProperties1: TcxCustomEditProperties;
|
||
const AValue1: Variant; ARow2: TcxGridDataRow;
|
||
AProperties2: TcxCustomEditProperties; const AValue2: Variant;
|
||
var AAreEqual: Boolean);
|
||
procedure V1Money15CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
procedure V1ZMoneyCompareRowValuesForCellMerging(Sender: TcxGridColumn;
|
||
ARow1: TcxGridDataRow; AProperties1: TcxCustomEditProperties;
|
||
const AValue1: Variant; ARow2: TcxGridDataRow;
|
||
AProperties2: TcxCustomEditProperties; const AValue2: Variant;
|
||
var AAreEqual: Boolean);
|
||
procedure V1ZJMoneyCompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
private
|
||
procedure InitGrid();
|
||
{ Private declarations }
|
||
public
|
||
{ Public declarations }
|
||
end;
|
||
|
||
var
|
||
frmJGPBCKKCList_JSYCXMgg: TfrmJGPBCKKCList_JSYCXMgg;
|
||
|
||
implementation
|
||
uses
|
||
U_DataLink,U_RTFun,U_JGSXKNewList_CPCKCX,U_CLShuiPiao_CX;
|
||
|
||
{$R *.dfm}
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.FormDestroy(Sender: TObject);
|
||
begin
|
||
frmJGPBCKKCList_JSYCXMgg:=nil;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.FormClose(Sender: TObject;
|
||
var Action: TCloseAction);
|
||
begin
|
||
Action:=caFree;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.InitGrid();
|
||
var FSJName:string;
|
||
begin
|
||
ToolBar1.SetFocus;
|
||
Panel2.Visible:=True;
|
||
Panel2.Refresh;
|
||
try
|
||
ADOQueryMain.DisableControls;
|
||
with ADOQueryMain do
|
||
begin
|
||
Filtered:=False;
|
||
Close;
|
||
sql.Clear;
|
||
if RadioGroup1.ItemIndex=1 then
|
||
begin
|
||
sql.Add('select AAA.*,ZJMoney=ZYFMoney+isnull(Money14,0)+isnull(Money15,0)+isnull(ZMoney16,0)+isnull(ZMoney17,0)+isnull(ZYHMoney,0)');//+isnull(Money18,0)
|
||
sql.Add(',ZMoney=ZYFMoney+isnull(Money14,0)+isnull(Money15,0)+isnull(ZMoney16,0)+isnull(ZMoney17,0) '); //+isnull(Money18,0)
|
||
sql.Add(' from (select AA.*,CE=(ZMoney13-Money13)');
|
||
{
|
||
sql.Add(',Money14=(case when ZCQty>=150 and ZCQty<=180 then (ZCQty-150)*5 when ZCQty>180 then 150 else 0 end)');
|
||
sql.Add(',Money18=(case when ZCQty>=180 and ZCQty<=200 then (ZCQty-180)*8 when ZCQty>200 then 160 else 0 end)');
|
||
sql.Add(',Money15=(case when ZCQty>200 then (ZCQty-200)*10 else 0 end)');
|
||
sql.add(',Qty14=(case when ZCQty>=150 and ZCQty<=180 then ZCQty-150 when ZCQty>180 then 30 else 0 end)');
|
||
sql.Add(',Qty18=(case when ZCQty>=180 and ZCQty<=200 then ZCQty-180 when ZCQty>200 then 20 else 0 end)');
|
||
sql.Add(',Qty15=(case when ZCQty>200 then ZCQty-200 else 0 end)');
|
||
}
|
||
|
||
{//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>121-150 15Ԫ 150<35><30><EFBFBD><EFBFBD> 20Ԫ
|
||
sql.Add(',Money14=(case when ZCQty>=121 and ZCQty<=150 then (ZCQty-120)*15 when ZCQty>150 then 450 else 0 end)');
|
||
sql.Add(',Money15=(case when ZCQty>150 then (ZCQty-150)*20 else 0 end)');
|
||
sql.add(',Qty14=(case when ZCQty>=121 and ZCQty<=150 then ZCQty-120 when ZCQty>150 then 30 else 0 end)');
|
||
sql.Add(',Qty15=(case when ZCQty>150 then ZCQty-150 else 0 end)');
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>121-150 15Ԫ 150<35><30><EFBFBD><EFBFBD> 20Ԫ }
|
||
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>121-150 20Ԫ 150<35><30><EFBFBD><EFBFBD> 25Ԫ20230515
|
||
{ sql.Add(',Money14=(case when ZCQty>=121 and ZCQty<=150 then (ZCQty-120)*20 when ZCQty>150 then 600 else 0 end)');
|
||
sql.Add(',Money15=(case when ZCQty>150 then (ZCQty-150)*25 else 0 end)');
|
||
sql.add(',Qty14=(case when ZCQty>=121 and ZCQty<=150 then ZCQty-120 when ZCQty>150 then 30 else 0 end)');
|
||
sql.Add(',Qty15=(case when ZCQty>150 then ZCQty-150 else 0 end)');
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>121-150 20Ԫ 150<35><30><EFBFBD><EFBFBD> 25Ԫ20230515 }
|
||
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>101-120 20Ԫ 120<32><30><EFBFBD><EFBFBD> 25Ԫ20240517
|
||
sql.Add(',Money14=(case when ZCQty>=101 and ZCQty<=120 then (ZCQty-100)*20 when ZCQty>120 then 600 else 0 end)');
|
||
sql.Add(',Money15=(case when ZCQty>120 then (ZCQty-120)*25 else 0 end)');
|
||
sql.add(',Qty14=(case when ZCQty>=101 and ZCQty<=120 then ZCQty-100 when ZCQty>120 then 30 else 0 end)');
|
||
sql.Add(',Qty15=(case when ZCQty>120 then ZCQty-120 else 0 end)');
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>101-120 20Ԫ 120<32><30><EFBFBD><EFBFBD> 25Ԫ20240517
|
||
|
||
|
||
|
||
sql.Add(' from (select A.CheHao,A.CLSpec,A.SJName,convert(varchar(7),A.CRTime,120) CRtime,Count(A.CPID) ZCC,Sum(YFMoney) YFMoney');
|
||
sql.Add(',Qty1=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=5 and B.GLQty>=1)');
|
||
sql.Add(',Price1=(case when A.CLSpec=''9<><39>'' then 15 else 17 end)');
|
||
sql.Add(',Money1=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=5 and B.GLQty>=1)');
|
||
sql.Add(',Qty2=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=7.5 and B.GLQty>5)');
|
||
sql.Add(',Price2=(case when A.CLSpec=''9<><39>'' then 16 else 18 end)');
|
||
sql.Add(',Money2=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=7.5 and B.GLQty>5)');
|
||
sql.Add(',Qty3=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=10 and B.GLQty>7.5)');
|
||
sql.Add(',Price3=(case when A.CLSpec=''9<><39>'' then 17 else 19 end)');
|
||
sql.Add(',Money3=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=10 and B.GLQty>7.5)');
|
||
sql.Add(',Qty4=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=12.5 and B.GLQty>10)');
|
||
sql.Add(',Price4=(case when A.CLSpec=''9<><39>'' then 18 else 20 end)');
|
||
sql.Add(',Money4=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=12.5 and B.GLQty>10)');
|
||
sql.Add(',Qty5=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=15 and B.GLQty>12.5)');
|
||
sql.Add(',Price5=(case when A.CLSpec=''9<><39>'' then 19 else 21 end)');
|
||
sql.Add(',Money5=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=15 and B.GLQty>12.5)');
|
||
sql.Add(',Qty6=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=17.5 and B.GLQty>15)');
|
||
sql.Add(',Price6=(case when A.CLSpec=''9<><39>'' then 20 else 22 end)');
|
||
sql.Add(',Money6=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=17.5 and B.GLQty>15)');
|
||
sql.Add(',Qty7=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=20 and B.GLQty>17.5)');
|
||
sql.Add(',Price7=(case when A.CLSpec=''9<><39>'' then 21 else 23 end)');
|
||
sql.Add(',Money7=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=20 and B.GLQty>17.5)');
|
||
sql.Add(',Qty8=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=22.5 and B.GLQty>20)');
|
||
sql.Add(',Price8=(case when A.CLSpec=''9<><39>'' then 22 else 24 end)');
|
||
sql.Add(',Money8=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=22.5 and B.GLQty>20)');
|
||
sql.Add(',Qty9=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=25 and B.GLQty>22.5)');
|
||
sql.Add(',Price9=(case when A.CLSpec=''9<><39>'' then 23 else 25 end)');
|
||
sql.Add(',Money9=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=25 and B.GLQty>22.5)');
|
||
sql.Add(',Qty10=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=27.5 and B.GLQty>25)');
|
||
sql.Add(',Price10=(case when A.CLSpec=''9<><39>'' then 24 else 26 end)');
|
||
sql.Add(',Money10=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=27.5 and B.GLQty>25)');
|
||
sql.Add(',Qty11=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=30 and B.GLQty>27.5)');
|
||
sql.Add(',Price11=(case when A.CLSpec=''9<><39>'' then 25 else 27 end)');
|
||
sql.Add(',Money11=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=30 and B.GLQty>27.5)');
|
||
sql.Add(',Qty12=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=32.5 and B.GLQty>30)');
|
||
sql.Add(',Price12=(case when A.CLSpec=''9<><39>'' then 26 else 28 end)');
|
||
sql.Add(',Money12=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty<=32.5 and B.GLQty>30)');
|
||
sql.Add(',Qty13=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty>32.5)');
|
||
sql.Add(',Price13=(case when A.CLSpec=''9<><39>'' then 27 else 29 end)');
|
||
sql.Add(',Money13=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty>32.5)*(case when A.CLSpec=''9<><39>'' then 27 else 29 end)');
|
||
sql.Add(',ZMoney13=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.GLQty>32.5)');
|
||
|
||
sql.Add(',ZCQty=(select Count(B.CPID) from CK_CP_CR B where B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120))');
|
||
sql.Add(',ZYFMoney=(select Sum(B.YFMoney) from CK_CP_CR B where B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120))');
|
||
//sql.Add(',ZMoney16=(select Count(B.CPID) from CK_CP_CR B where B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.SJType=''ҹ<><D2B9>'' and CLName<>''<27>ó<EFBFBD>'')*10');
|
||
//ҹ<><D2B9><EFBFBD><EFBFBD>ԭ<EFBFBD><D4AD><EFBFBD><EFBFBD>10Ԫ<30><D4AA><EFBFBD><EFBFBD>Ϊ15Ԫ
|
||
sql.Add(',ZMoney16=(select Count(B.CPID) from CK_CP_CR B where B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.SJType=''ҹ<><D2B9>'' and CLName<>''<27>ó<EFBFBD>'')*15');
|
||
sql.Add(',ZQty17=(select Sum(B.SPQty) from CL_SP_Main B where B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120))');
|
||
sql.Add(',ZMoney17=(select Sum(B.SPQty) from CL_SP_Main B where B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120))*20');
|
||
sql.Add(',ZYHMoney=(select Sum(B.SFMoney) from CL_YHKH B where B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120))');
|
||
sql.Add(',Price14=cast(20 as decimal(18,2))');
|
||
sql.Add(',Price15=cast(25 as decimal(18,2))');
|
||
|
||
sql.Add(',Qty16=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.SJType=''ҹ<><D2B9>'' and CLName<>''<27>ó<EFBFBD>'')');
|
||
sql.Add(',Price16=cast(15 as decimal(18,2))');
|
||
//sql.Add(',Money16=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.SJType=''ҹ<><D2B9>'' and CLName<>''<27>ó<EFBFBD>'')*10');
|
||
//ҹ<><D2B9><EFBFBD><EFBFBD>ԭ<EFBFBD><D4AD><EFBFBD><EFBFBD>10Ԫ<30><D4AA><EFBFBD><EFBFBD>Ϊ15Ԫ
|
||
sql.Add(',Money16=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120) and B.SJType=''ҹ<><D2B9>'' and CLName<>''<27>ó<EFBFBD>'')*15');
|
||
sql.Add(',Qty17=(select Sum(B.SPQty) from CL_SP_Main B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120))');
|
||
sql.Add(',Price17=cast(20 as decimal(18,2))');
|
||
sql.Add(',Money17=(select Sum(B.SPQty) from CL_SP_Main B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120))*20');
|
||
sql.Add(',YHMoney=(select Sum(B.SFMoney) from CL_YHKH B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(7),A.CRTime,120)=convert(varchar(7),B.CRTime,120))');
|
||
sql.Add(' from CK_CP_CR A ');
|
||
sql.Add('where A.CLSpec in (''9<><39>'',''12<31><32>'',''13<31><33>'') ');
|
||
if CheHao.Text<>'' then
|
||
begin
|
||
sql.Add('and A.CheHao='''+trim(CheHao.Text)+''' ');
|
||
end;
|
||
if SJName.Text<>'' then
|
||
begin
|
||
sql.Add('and A.SJName like '''+'%'+trim(SJName.Text)+'%'+''' ');
|
||
end;
|
||
if cxTabControl1.TabIndex=0 then
|
||
sql.add('and isnull(A.HDStatus,'''')='''' ');
|
||
if cxTabControl1.TabIndex=1 then
|
||
sql.add('and isnull(A.HDStatus,'''')=''1'' ');
|
||
sql.Add('and convert(varchar(7),A.CRTime,120)='''+trim(FormatDateTime('yyyy-MM',BegDate.date))+'''');
|
||
sql.Add(' group by A.CheHao,A.CLSpec,A.SJName,convert(varchar(7),A.CRTime,120)) AA) AAA');
|
||
sql.Add(' order by AAA.SJName,ZCC desc');
|
||
end;
|
||
if RadioGroup1.ItemIndex=0 then
|
||
begin
|
||
sql.Add('select AAA.*,ZJMoney=ZYFMoney+isnull(Money14,0)+isnull(Money15,0)+isnull(ZMoney16,0)+isnull(ZMoney17,0)+isnull(ZYHMoney,0)'); //+isnull(Money18,0)
|
||
sql.Add(',ZMoney=ZYFMoney+isnull(Money14,0)+isnull(Money15,0)+isnull(ZMoney16,0)+isnull(ZMoney17,0) ');//+isnull(Money18,0)
|
||
sql.Add(' from (select AA.*,CE=(ZMoney13-Money13)');
|
||
{
|
||
sql.Add(',Money14=(case when ZCQty>=150 and ZCQty<=180 then (ZCQty-150)*5 when ZCQty>180 then 150 else 0 end)');
|
||
sql.Add(',Money18=(case when ZCQty>=180 and ZCQty<=200 then (ZCQty-180)*8 when ZCQty>200 then 160 else 0 end)');
|
||
sql.Add(',Money15=(case when ZCQty>200 then (ZCQty-200)*10 else 0 end)');
|
||
sql.add(',Qty14=(case when ZCQty>=150 and ZCQty<=180 then ZCQty-150 when ZCQty>180 then 30 else 0 end)');
|
||
sql.Add(',Qty18=(case when ZCQty>=180 and ZCQty<=200 then ZCQty-180 when ZCQty>200 then 20 else 0 end)');
|
||
sql.Add(',Qty15=(case when ZCQty>200 then ZCQty-200 else 0 end)'); }
|
||
{
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>121-150 10Ԫ 150<35><30><EFBFBD><EFBFBD> 15Ԫ
|
||
sql.Add(',Money14=(case when ZCQty>=121 and ZCQty<=150 then (ZCQty-120)*10 when ZCQty>150 then 300 else 0 end)');
|
||
sql.Add(',Money15=(case when ZCQty>150 then (ZCQty-150)*15 else 0 end)');
|
||
sql.add(',Qty14=(case when ZCQty>=121 and ZCQty<=150 then ZCQty-120 when ZCQty>150 then 30 else 0 end)');
|
||
sql.Add(',Qty15=(case when ZCQty>150 then ZCQty-150 else 0 end)');
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>121-150 10Ԫ 150<35><30><EFBFBD><EFBFBD> 15Ԫ }
|
||
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>101-120 10Ԫ 120<32><30><EFBFBD><EFBFBD> 15Ԫ
|
||
sql.Add(',Money14=(case when ZCQty>=101 and ZCQty<=120 then (ZCQty-100)*10 when ZCQty>120 then 300 else 0 end)');
|
||
sql.Add(',Money15=(case when ZCQty>120 then (ZCQty-120)*15 else 0 end)');
|
||
sql.add(',Qty14=(case when ZCQty>=101 and ZCQty<=120 then ZCQty-100 when ZCQty>120 then 30 else 0 end)');
|
||
sql.Add(',Qty15=(case when ZCQty>120 then ZCQty-120 else 0 end)');
|
||
//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>101-120 10Ԫ 120<32><30><EFBFBD><EFBFBD> 15Ԫ
|
||
|
||
|
||
|
||
|
||
|
||
|
||
sql.Add(' from (select A.CheHao,A.CLSpec,A.SJName,convert(varchar(4),A.CRTime,120) CRtime,Count(A.CPID) ZCC,Sum(YFMoney) YFMoney');
|
||
sql.Add(',Qty1=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=5 and B.GLQty>=1)');
|
||
sql.Add(',Price1=(case when A.CLSpec=''9<><39>'' then 15 else 17 end)');
|
||
sql.Add(',Money1=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=5 and B.GLQty>=1)');
|
||
sql.Add(',Qty2=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=7.5 and B.GLQty>5)');
|
||
sql.Add(',Price2=(case when A.CLSpec=''9<><39>'' then 16 else 18 end)');
|
||
sql.Add(',Money2=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=7.5 and B.GLQty>5)');
|
||
sql.Add(',Qty3=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=10 and B.GLQty>7.5)');
|
||
sql.Add(',Price3=(case when A.CLSpec=''9<><39>'' then 17 else 19 end)');
|
||
sql.Add(',Money3=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=10 and B.GLQty>7.5)');
|
||
sql.Add(',Qty4=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=12.5 and B.GLQty>10)');
|
||
sql.Add(',Price4=(case when A.CLSpec=''9<><39>'' then 18 else 20 end)');
|
||
sql.Add(',Money4=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=12.5 and B.GLQty>10)');
|
||
sql.Add(',Qty5=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=15 and B.GLQty>12.5)');
|
||
sql.Add(',Price5=(case when A.CLSpec=''9<><39>'' then 19 else 21 end)');
|
||
sql.Add(',Money5=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=15 and B.GLQty>12.5)');
|
||
sql.Add(',Qty6=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=17.5 and B.GLQty>15)');
|
||
sql.Add(',Price6=(case when A.CLSpec=''9<><39>'' then 20 else 22 end)');
|
||
sql.Add(',Money6=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=17.5 and B.GLQty>15)');
|
||
sql.Add(',Qty7=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=20 and B.GLQty>17.5)');
|
||
sql.Add(',Price7=(case when A.CLSpec=''9<><39>'' then 21 else 23 end)');
|
||
sql.Add(',Money7=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=20 and B.GLQty>17.5)');
|
||
sql.Add(',Qty8=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=22.5 and B.GLQty>20)');
|
||
sql.Add(',Price8=(case when A.CLSpec=''9<><39>'' then 22 else 24 end)');
|
||
sql.Add(',Money8=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=22.5 and B.GLQty>20)');
|
||
sql.Add(',Qty9=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=25 and B.GLQty>22.5)');
|
||
sql.Add(',Price9=(case when A.CLSpec=''9<><39>'' then 23 else 25 end)');
|
||
sql.Add(',Money9=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=25 and B.GLQty>22.5)');
|
||
sql.Add(',Qty10=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=27.5 and B.GLQty>25)');
|
||
sql.Add(',Price10=(case when A.CLSpec=''9<><39>'' then 24 else 26 end)');
|
||
sql.Add(',Money10=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=27.5 and B.GLQty>25)');
|
||
sql.Add(',Qty11=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=30 and B.GLQty>27.5)');
|
||
sql.Add(',Price11=(case when A.CLSpec=''9<><39>'' then 25 else 27 end)');
|
||
sql.Add(',Money11=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=30 and B.GLQty>27.5)');
|
||
sql.Add(',Qty12=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=32.5 and B.GLQty>30)');
|
||
sql.Add(',Price12=(case when A.CLSpec=''9<><39>'' then 26 else 28 end)');
|
||
sql.Add(',Money12=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty<=32.5 and B.GLQty>30)');
|
||
sql.Add(',Qty13=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty>32.5)');
|
||
sql.Add(',Price13=(case when A.CLSpec=''9<><39>'' then 27 else 29 end)');
|
||
sql.Add(',Money13=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty>32.5)*(case when A.CLSpec=''9<><39>'' then 27 else 29 end)');
|
||
sql.Add(',ZMoney13=(select Sum(B.YFMoney) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.GLQty>32.5)');
|
||
|
||
sql.Add(',ZCQty=(select Count(B.CPID) from CK_CP_CR B where B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120))');
|
||
sql.Add(',ZYFMoney=(select Sum(B.YFMoney) from CK_CP_CR B where B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120))');
|
||
sql.Add(',ZMoney16=(select Count(B.CPID) from CK_CP_CR B where B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.SJType=''ҹ<><D2B9>'')*10');
|
||
sql.Add(',ZMoney17=(select Sum(B.SPQty) from CL_SP_Main B where B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120))*20');
|
||
sql.Add(',ZYHMoney=(select Sum(B.SFMoney) from CL_YHKH B where B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120))');
|
||
sql.Add(',Price14=cast(5 as decimal(18,2))');
|
||
sql.Add(',Price18=cast(8 as decimal(18,2))');
|
||
sql.Add(',Price15=cast(10 as decimal(18,2))');
|
||
|
||
sql.Add(',Qty16=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.SJType=''ҹ<><D2B9>'')');
|
||
sql.Add(',Price16=cast(10 as decimal(18,2))');
|
||
sql.Add(',Money16=(select Count(B.CPID) from CK_CP_CR B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120) and B.SJType=''ҹ<><D2B9>'')*10');
|
||
sql.Add(',Qty17=(select Sum(B.SPQty) from CL_SP_Main B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120))');
|
||
sql.Add(',Price17=cast(20 as decimal(18,2))');
|
||
sql.Add(',Money17=(select Sum(B.SPQty) from CL_SP_Main B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120))*20');
|
||
sql.Add(',YHMoney=(select Sum(B.SFMoney) from CL_YHKH B where B.CheHao=A.CheHao and B.CLSpec=A.CLSpec and B.SJName=A.SJName and convert(varchar(4),A.CRTime,120)=convert(varchar(4),B.CRTime,120))');
|
||
sql.Add(' from CK_CP_CR A ');
|
||
sql.Add('where A.CLSpec in (''9<><39>'',''12<31><32>'',''13<31><33>'') ');
|
||
if CheHao.Text<>'' then
|
||
begin
|
||
sql.Add('and A.CheHao='''+trim(CheHao.Text)+''' ');
|
||
end;
|
||
if SJName.Text<>'' then
|
||
begin
|
||
sql.Add('and A.SJName like '''+'%'+trim(SJName.Text)+'%'+''' ');
|
||
end;
|
||
if cxTabControl1.TabIndex=0 then
|
||
sql.add('and isnull(A.HDStatus,'''')='''' ');
|
||
if cxTabControl1.TabIndex=1 then
|
||
sql.add('and isnull(A.HDStatus,'''')=''1'' ');
|
||
sql.Add('and convert(varchar(4),A.CRTime,120)='''+trim(FormatDateTime('yyyy',BegDate.date))+'''');
|
||
sql.Add(' group by A.CheHao,A.CLSpec,A.SJName,convert(varchar(4),A.CRTime,120)) AA)AAA');
|
||
sql.Add(' order by AAA.SJName,ZCC desc');
|
||
end;
|
||
//ShowMessage(sql.Text);
|
||
Open;
|
||
end;
|
||
SCreateCDS20(ADOQueryMain,CDS_Main);
|
||
SInitCDSData20(ADOQueryMain,CDS_Main);
|
||
with CDS_Main do
|
||
begin
|
||
First;
|
||
while not eof do
|
||
begin
|
||
if FSJName='' then
|
||
FSJName:=fieldbyname('SJName').AsString
|
||
else
|
||
begin
|
||
if FSJName=fieldbyname('SJName').AsString then
|
||
begin
|
||
Edit;
|
||
FieldByName('Qty14').Value:=NULL;
|
||
FieldByName('Money14').Value:=NULL;
|
||
//FieldByName('Qty18').Value:=NULL;
|
||
// FieldByName('Money18').Value:=NULL;
|
||
FieldByName('Qty15').Value:=NULL;
|
||
FieldByName('Money15').Value:=NULL;
|
||
FieldByName('ZMoney').Value:=NULL;
|
||
FieldByName('ZJMoney').Value:=NULL;
|
||
post;
|
||
end
|
||
else
|
||
begin
|
||
FSJName:=fieldbyname('SJName').AsString;
|
||
end;
|
||
end;
|
||
next;
|
||
end;
|
||
end;
|
||
finally;
|
||
ADOQueryMain.EnableControls;
|
||
end;
|
||
Panel2.Visible:=false;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.TBRafreshClick(Sender: TObject);
|
||
begin
|
||
ToolBar1.SetFocus;
|
||
InitGrid();
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.ConNoMChange(Sender: TObject);
|
||
begin
|
||
if ADOQueryMain.Active then
|
||
begin
|
||
SDofilter(ADOQueryMain,SGetFilters(Panel1,1,2));
|
||
end;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.TBCloseClick(Sender: TObject);
|
||
begin
|
||
WriteCxBandedGrid('<27><>ʻԱ<CABB><D4B1>ϸ21',Tv1,'ԭ<>ϲֿ<CFB2>');
|
||
Close;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.FormShow(Sender: TObject);
|
||
begin
|
||
ReadCxBandedGrid('<27><>ʻԱ<CABB><D4B1>ϸ21',Tv1,'ԭ<>ϲֿ<CFB2>');
|
||
BegDate.Date:=SGetServerDate(ADOQueryTemp);
|
||
//InitGrid();
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.TBFindClick(Sender: TObject);
|
||
begin
|
||
InitGrid();
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.MPRTCodeNameChange(Sender: TObject);
|
||
begin
|
||
TBFind.Click;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.N1Click(Sender: TObject);
|
||
begin
|
||
SelOKNo(CDS_Main,True);
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.N2Click(Sender: TObject);
|
||
begin
|
||
SelOKNo(CDS_Main,False);
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.SXBatchNOChange(Sender: TObject);
|
||
begin
|
||
TBFind.Click;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.FactoryNameChange(Sender: TObject);
|
||
begin
|
||
TBFind.Click;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.Tv1CellDblClick(Sender: TcxCustomGridTableView;
|
||
ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
|
||
AShift: TShiftState; var AHandled: Boolean);
|
||
begin
|
||
ModalResult:=1;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.ToolButton2Click(Sender: TObject);
|
||
begin
|
||
ToolBar1.SetFocus;
|
||
if CDS_Main.IsEmpty then exit;
|
||
TcxGridToExcel('<27><>ʻԱ<CABB><D4B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',cxGrid2);
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.RadioGroup1Click(Sender: TObject);
|
||
begin
|
||
InitGrid();
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.TFHCXClick(Sender: TObject);
|
||
begin
|
||
if CDS_Main.IsEmpty then exit;
|
||
frmJGSXNewList_CPCKCX:=TfrmJGSXNewList_CPCKCX.Create(self);
|
||
with frmJGSXNewList_CPCKCX do
|
||
begin
|
||
formid:='1';
|
||
BegDate.Date:=StrToDate(trim(FormatDateTime('yyyy-MM',self.BegDate.Date)+'-01'));
|
||
endDate.Date:=StrToDate(trim(FormatDateTime('yyyy-',self.BegDate.Date))+inttostr(StrToint(trim(FormatDateTime('MM',self.BegDate.Date)))+1)+'-01')-1;
|
||
CLSpec.Text:=Trim(Self.CDS_Main.fieldbyname('CLSpec').AsString);
|
||
CheHao.Text:=Trim(Self.CDS_Main.fieldbyname('CheHao').AsString);
|
||
SJName.Text:=Trim(Self.CDS_Main.fieldbyname('SJName').AsString);
|
||
CRType.ItemIndex:=0;
|
||
if ShowModal=1 then
|
||
begin
|
||
|
||
end;
|
||
free;
|
||
end;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.TSPCXClick(Sender: TObject);
|
||
begin
|
||
if CDS_Main.IsEmpty then exit;
|
||
frmCLShuiPiao_CX:=TfrmCLShuiPiao_CX.Create(self);
|
||
with frmCLShuiPiao_CX do
|
||
begin
|
||
formid:='1';
|
||
BegDate.Date:=StrToDate(trim(FormatDateTime('yyyy-MM',self.BegDate.Date)+'-01'));
|
||
endDate.Date:=StrToDate(trim(FormatDateTime('yyyy-',self.BegDate.Date))+inttostr(StrToint(trim(FormatDateTime('MM',self.BegDate.Date)))+1)+'-01')-1;
|
||
CLSpec.Text:=Trim(Self.CDS_Main.fieldbyname('CLSpec').AsString);
|
||
CheHao.Text:=Trim(Self.CDS_Main.fieldbyname('CheHao').AsString);
|
||
SJName.Text:=Trim(Self.CDS_Main.fieldbyname('SJName').AsString);
|
||
if ShowModal=1 then
|
||
begin
|
||
|
||
end;
|
||
free;
|
||
end;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.cxTabControl1Change(Sender: TObject);
|
||
begin
|
||
THDWC.Visible:=False;
|
||
TCXHD.Visible:=false;
|
||
if cxTabControl1.TabIndex=0 then
|
||
begin
|
||
THDWC.Visible:=true;
|
||
end;
|
||
if cxTabControl1.TabIndex=1 then
|
||
begin
|
||
TCXHD.Visible:=true;
|
||
end;
|
||
TBRafresh.Click;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.THDWCClick(Sender: TObject);
|
||
var maxno:string;
|
||
begin
|
||
if CDS_Main.IsEmpty then exit;
|
||
if CDS_Main.Locate('Ssel',true,[])=False then
|
||
begin
|
||
Application.MessageBox('û<><C3BB>ѡ<EFBFBD><D1A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','<27><>ʾ');
|
||
exit;
|
||
end;
|
||
with CDS_Main do
|
||
begin
|
||
First;
|
||
while not eof do
|
||
begin
|
||
if CDS_Main.FieldByName('Ssel').AsBoolean=true then
|
||
begin
|
||
with ADOQueryCmd do
|
||
begin
|
||
close;
|
||
sql.Clear;
|
||
SQL.Add('update CK_CP_CR set HDStatus=''1'' ');
|
||
sql.Add('where SJName='''+trim(CDS_Main.fieldbyname('SJName').AsString)+'''');
|
||
SQL.Add(' and convert(varchar(7),CRTime,120)='''+trim(CDS_Main.fieldbyname('CRtime').AsString)+'''');
|
||
sql.Add(' and CLSpec<>''16<31><36>'' and CLSpec<>''17<31><37>''');
|
||
ExecSQL;
|
||
end;
|
||
with ADOQueryCmd do
|
||
begin
|
||
close;
|
||
sql.Clear;
|
||
sql.Add('select * from JSYGZHD_Year ');
|
||
sql.Add('where SJName='''+trim(CDS_Main.fieldbyname('SJName').AsString)+'''');
|
||
SQL.Add(' and HDMonth='''+trim(CDS_Main.fieldbyname('CRtime').AsString)+'''');
|
||
SQL.Add(' and CLSpec=''С<><D0A1>''');
|
||
Open;
|
||
end;
|
||
with ADOQueryCmd do
|
||
begin
|
||
if IsEmpty then
|
||
begin
|
||
if GetLSNo(ADOQueryTemp,Maxno,'HD','JSYGZHD_Year',3,1)=False then
|
||
begin
|
||
Application.MessageBox('ȡ<><C8A1><EFBFBD>ʺ˶Ա<CBB6><D4B1><EFBFBD>ʧ<EFBFBD><CAA7>!','<27><>ʾ',0);
|
||
Exit;
|
||
end;
|
||
append;
|
||
FieldByName('JJMoney').Value:=CDS_Main.fieldbyname('Money14').AsFloat+CDS_Main.fieldbyname('Money15').AsFloat
|
||
+CDS_Main.fieldbyname('ZMoney16').AsFloat+CDS_Main.fieldbyname('Money18').AsFloat;
|
||
FieldByName('ZJMoney').Value:=CDS_Main.fieldbyname('ZJMoney').AsFloat;
|
||
end
|
||
else
|
||
begin
|
||
edit;
|
||
maxno:=Trim(ADOQueryCmd.fieldbyname('HDID').AsString);
|
||
end;
|
||
FieldByName('HDID').Value:=Trim(maxno);
|
||
FieldByName('CLSpec').Value:='С<><D0A1>';
|
||
FieldByName('SJName').Value:=Trim(CDS_Main.fieldbyname('SJName').AsString);
|
||
FieldByName('HDMonth').Value:=Trim(CDS_Main.fieldbyname('CRtime').AsString);
|
||
FieldByName('filler').Value:=Trim(DName);
|
||
FieldByName('CCMoney').Value:=CDS_Main.fieldbyname('ZYFMoney').AsFloat;
|
||
FieldByName('CCQty').Value:=CDS_Main.fieldbyname('ZCQty').AsInteger;
|
||
FieldByName('SPQty').Value:=CDS_Main.fieldbyname('ZQty17').AsInteger;
|
||
FieldByName('SPMoney').Value:=CDS_Main.fieldbyname('ZMoney17').AsFloat;
|
||
FieldByName('YHMoney').Value:=CDS_Main.fieldbyname('ZYHMoney').AsFloat;
|
||
post;
|
||
end;
|
||
CDS_Main.Delete;
|
||
end
|
||
else
|
||
next;
|
||
end;
|
||
end;
|
||
Application.MessageBox('<27><><EFBFBD><EFBFBD><EFBFBD>ɹ<EFBFBD>','<27><>ʾ');
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.TCXHDClick(Sender: TObject);
|
||
begin
|
||
if CDS_Main.IsEmpty then exit;
|
||
if CDS_Main.Locate('Ssel',true,[])=False then
|
||
begin
|
||
Application.MessageBox('û<><C3BB>ѡ<EFBFBD><D1A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>','<27><>ʾ');
|
||
exit;
|
||
end;
|
||
with CDS_Main do
|
||
begin
|
||
First;
|
||
while not eof do
|
||
begin
|
||
if CDS_Main.FieldByName('Ssel').AsBoolean=true then
|
||
begin
|
||
with ADOQueryCmd do
|
||
begin
|
||
close;
|
||
sql.Clear;
|
||
SQL.Add('update CK_CP_CR set HDStatus='''' ');
|
||
sql.Add('where SJName='''+trim(CDS_Main.fieldbyname('SJName').AsString)+'''');
|
||
SQL.Add(' and convert(varchar(7),CRTime,120)='''+trim(CDS_Main.fieldbyname('CRtime').AsString)+'''');
|
||
sql.Add(' and CLSpec<>''16<31><36>'' and CLSpec<>''17<31><37>''');
|
||
ExecSQL;
|
||
end;
|
||
with ADOQueryCmd do
|
||
begin
|
||
close;
|
||
sql.Clear;
|
||
sql.Add('delete from JSYGZHD_Year ');
|
||
sql.Add('where SJName='''+trim(CDS_Main.fieldbyname('SJName').AsString)+'''');
|
||
SQL.Add(' and HDMonth='''+trim(CDS_Main.fieldbyname('CRtime').AsString)+'''');
|
||
SQL.Add(' and CLSpec=''С<><D0A1>''');
|
||
ExecSQL;
|
||
end;
|
||
CDS_Main.Delete;
|
||
end
|
||
else
|
||
next;
|
||
end;
|
||
end;
|
||
application.MessageBox('<27><><EFBFBD><EFBFBD><EFBFBD>ɹ<EFBFBD>','<27><>ʾ');
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.V1Qty14CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
begin
|
||
inherited;
|
||
if ARow1.Values[2] = ARow2.Values[2] then
|
||
AAreEqual := True
|
||
else
|
||
AAreEqual := False;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.V1Money14CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
begin
|
||
inherited;
|
||
if ARow1.Values[2] = ARow2.Values[2] then
|
||
AAreEqual := True
|
||
else
|
||
AAreEqual := False;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.V1Qty18CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
begin
|
||
inherited;
|
||
if ARow1.Values[2] = ARow2.Values[2] then
|
||
AAreEqual := True
|
||
else
|
||
AAreEqual := False;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.V1Money18CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
begin
|
||
inherited;
|
||
if ARow1.Values[2] = ARow2.Values[2] then
|
||
AAreEqual := True
|
||
else
|
||
AAreEqual := False;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.V1Qty15CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
begin
|
||
inherited;
|
||
if ARow1.Values[2] = ARow2.Values[2] then
|
||
AAreEqual := True
|
||
else
|
||
AAreEqual := False;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.V1Money15CompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
begin
|
||
inherited;
|
||
if ARow1.Values[2] = ARow2.Values[2] then
|
||
AAreEqual := True
|
||
else
|
||
AAreEqual := False;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.V1ZMoneyCompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
begin
|
||
inherited;
|
||
if ARow1.Values[2] = ARow2.Values[2] then
|
||
AAreEqual := True
|
||
else
|
||
AAreEqual := False;
|
||
end;
|
||
|
||
procedure TfrmJGPBCKKCList_JSYCXMgg.V1ZJMoneyCompareRowValuesForCellMerging(
|
||
Sender: TcxGridColumn; ARow1: TcxGridDataRow;
|
||
AProperties1: TcxCustomEditProperties; const AValue1: Variant;
|
||
ARow2: TcxGridDataRow; AProperties2: TcxCustomEditProperties;
|
||
const AValue2: Variant; var AAreEqual: Boolean);
|
||
begin
|
||
inherited;
|
||
if ARow1.Values[2] = ARow2.Values[2] then
|
||
AAreEqual := True
|
||
else
|
||
AAreEqual := False;
|
||
end;
|
||
|
||
end.
|