📄 sql2.txt
字号:
{/*
查询本班入库数据
*/}
for i:=4 to grid1.RowCount-2 do
begin
param.Clear;
param.Add(_whichStore);
param.Add(trim(grid1.cells[2,i].foretext));
param.Add(trim(grid1.cells[3,i].foretext));
param.Add(trim(grid1.cells[4,i].foretext));
param.Add(_CheckDate);
strsql:='select sum(convert(real,SI_productNum)) as a,sum(convert(real,SI_bottleNum)) as b,sum(convert(real,SI_EmptyBoxNum)) as c from Sale_intoStore where SI_whichStore=:s1 and ';
strsql:=strsql+'SI_productName=:s2 and SI_specification=:s3 and SI_UnitName=:s4 and SI_intodate=:s5';
grid1.Cells[8,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
grid1.Cells[9,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'b');
grid1.Cells[10,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'c');
end;
{/*
查询本班付出:包括三部分1.当天酒库调票数量
2.当天事故单
3.当天样品票
*/}
for i:=4 to grid1.RowCount-2 do
begin
param.Clear;
param.Add(_whichStore);
param.Add(trim(grid1.cells[2,i].foretext)+'∕'+'1'+'×'+trim(grid1.cells[3,i].foretext)+'--'+trim(grid1.cells[4,i].foretext));
param.Add('1'+'×'+trim(grid1.cells[3,i].foretext));
param.Add(trim(grid1.cells[4,i].foretext));
param.Add(_CheckDate);
strsql:='select sum(saleNum) as a from Sale_changeBill where whichStore=:s1 and ';
strsql:=strsql+'productName=:s2 and specification=:s3 and UnitName=:s4 and substring(outStoreDate,1,10)=:s5';
grid1.Cells[11,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
end;
for i:=4 to grid1.RowCount-2 do
begin
param.Clear;
param.Add(_whichStore);
param.Add(trim(grid1.cells[2,i].foretext));
param.Add(trim(grid1.cells[3,i].foretext));
param.Add(trim(grid1.cells[4,i].foretext));
param.Add(_CheckDate);
//param.Add('YP');
strsql:='select sum(convert(real,productNum)) as a,sum(convert(real,bottleNum)) as b from Sale_newSample_Office where whichStore=:s1 and ';
strsql:=strsql+'productName=:s2 and specification=:s3 and UnitName=:s4 and outStoreTime=:s5';
grid1.Cells[12,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
grid1.Cells[13,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'b');
end;
for i:=4 to grid1.RowCount-2 do
begin
param.Clear;
param.Add(_whichStore);
param.Add(trim(grid1.cells[2,i].foretext));
param.Add(trim(grid1.cells[3,i].foretext));
param.Add(trim(grid1.cells[4,i].foretext));
param.Add(_CheckDate);
//param.Add('SG');
strsql:='select sum(convert(real,productNum)) as a,sum(convert(real,bottleNum)) as b from Sale_newSample_Accident where whichStore=:s1 and ';
strsql:=strsql+'productName=:s2 and specification=:s3 and UnitName=:s4 and outStoreTime=:s5';
grid1.Cells[14,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
grid1.Cells[15,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'b');
end;
{/*
查询损耗数据
*/}
for i:=4 to grid1.RowCount-2 do
begin
param.Clear;
param.Add(_whichStore);
param.Add(_CheckDate);
param.Add(trim(grid1.cells[2,i].foretext));
param.Add(trim(grid1.cells[3,i].foretext));
param.Add(trim(grid1.cells[4,i].foretext));
strsql:='select sum(convert(real,SW_bottleNum1)) as a,sum(convert(real,SW_bottleNum2)) as b, ';
strsql:=strsql+'sum(convert(real,SW_bottleNum3)) as c from sale_wasteBill where ';
strsql:=strsql+'SW_whichStore=:s1 and substring(SW_CheckDate,1,10)=:s2 ';
strsql:=strsql+' and SW_produceName=:s3 and SW_Specification=:s4 ';
strsql:=strsql+' and SW_unitName=:s5 ';
grid1.Cells[16,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
grid1.Cells[17,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'b');
grid1.Cells[18,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'c');
end;
{/*
计算出电脑计算的库存数量=接班库存+入库-付出
*/}
for i:=4 to grid1.RowCount-2 do
begin
if grid1.Cells[5,i].ForeText<>'' then
begin
_Col_5Num:=strToFloat(grid1.Cells[5,i].ForeText);
end
else
_Col_5Num:=0;
if grid1.Cells[8,i].ForeText<>'' then
begin
_Col_7Num:=strToFloat(grid1.Cells[8,i].ForeText);
end
else
_Col_7Num:=0;
if grid1.Cells[11,i].ForeText<>'' then
begin
_Col_9Num:=strToFloat(grid1.Cells[11,i].ForeText);
end
else
_Col_9Num:=0;
if grid1.Cells[12,i].ForeText<>'' then
begin
_Col_10Num:=strToFloat(grid1.Cells[12,i].ForeText);
end
else
_Col_10Num:=0;
if grid1.Cells[14,i].ForeText<>'' then
begin
_Col_12Num:=strToFloat(grid1.Cells[14,i].ForeText);
end
else
_Col_12Num:=0;
if grid1.Cells[6,i].ForeText<>'' then
begin
_ColE1:=strToFloat(grid1.Cells[6,i].ForeText);
end
else
_ColE1:=0;
if grid1.Cells[9,i].ForeText<>'' then
begin
_ColE2:=strToFloat(grid1.Cells[9,i].ForeText);
end
else
_ColE2:=0;
////
if grid1.Cells[7,i].ForeText<>'' then
begin
_Col_6Num:=strToFloat(grid1.Cells[7,i].ForeText);
end
else
_Col_6Num:=0;
if grid1.Cells[10,i].ForeText<>'' then
begin
_Col_8Num:=strToFloat(grid1.Cells[10,i].ForeText);
end
else
_Col_8Num:=0;
if grid1.Cells[13,i].ForeText<>'' then
begin
_Col_11Num:=strToFloat(grid1.Cells[13,i].ForeText);
end
else
_Col_11Num:=0;
if grid1.Cells[15,i].ForeText<>'' then
begin
_Col_13Num:=strToFloat(grid1.Cells[15,i].ForeText);
end
else
_Col_13Num:=0;
if grid1.Cells[16,i].ForeText<>'' then
begin
_Col_14Num:=strToFloat(grid1.Cells[16,i].ForeText);
end
else
_Col_14Num:=0;
if grid1.Cells[17,i].ForeText<>'' then
begin
_Col_15Num:=strToFloat(grid1.Cells[17,i].ForeText);
end
else
_Col_15Num:=0;
if grid1.Cells[18,i].ForeText<>'' then
begin
_Col_16Num:=strToFloat(grid1.Cells[18,i].ForeText);
end
else
_Col_16Num:=0;
grid1.Cells[19,i].ForeText:=formatFloat('##0',_Col_5Num+_Col_7Num-_Col_9Num-_Col_10Num-_Col_12Num);
grid1.Cells[20,i].ForeText:=formatFloat('##0',_ColE1+_ColE2);
grid1.Cells[21,i].ForeText:=formatFloat('##0',_Col_6Num+_Col_8Num-_Col_11Num-_Col_13Num-_Col_14Num-_Col_15Num-_Col_16Num);
end;
{/*
得到交班时输入的交班盘点库存
*/}
for i:=4 to grid1.RowCount-2 do
begin
param.clear;
param.Add(_whichStore);
param.add(_CheckDate);
param.Add(trim(grid1.cells[2,i].foretext));
param.Add(trim(grid1.cells[3,i].foretext));
param.Add(trim(grid1.cells[4,i].foretext));
strsql:='select sum(convert(real,SF_boxNum)) as a,sum(convert(real,SF_bottleNum)) as b, sum(convert(real,SF_EmptyBoxNum)) as c from sale_handRealStore ';
strsql:=strsql+'where SF_whichStore=:s1 and substring(SF_CheckDate,1,10)=:s2 and SF_produceName=:s3 ';
strsql:=strsql+' and SF_specification=:s4 and SF_UnitName=:s5';
grid1.cells[22,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
grid1.cells[23,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'c');
grid1.cells[24,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'b');
end;
{/*
计算电脑的库存与实际盘点库存的差额
Col21=Col17-Col19
Col22=Col18-Col20
*/}
for i:=4 to grid1.RowCount-2 do
begin
if grid1.cells[19,i].foretext<>'' then
begin
_boxNum:=strToInt(grid1.cells[19,i].foretext);
end
else
_boxNum:=0;
if grid1.cells[22,i].foretext<>'' then
begin
_boxNum1:=strToInt(grid1.cells[22,i].foretext);
end
else
_boxNum1:=0;
////////
if grid1.cells[20,i].foretext<>'' then
begin
_EmptyboxNum:=strToInt(grid1.cells[20,i].foretext);
end
else
_EmptyboxNum:=0;
if grid1.cells[23,i].foretext<>'' then
begin
_EmptyboxNum1:=strToInt(grid1.cells[23,i].foretext);
end
else
_EmptyboxNum1:=0;
///////
if grid1.cells[21,i].foretext<>'' then
begin
_bottleNum:=strToInt(grid1.cells[21,i].foretext);
end
else
_bottleNum:=0;
if grid1.cells[24,i].foretext<>'' then
begin
_bottleNum1:=strToInt(grid1.cells[24,i].foretext);
end
else
_bottleNum1:=0;
grid1.cells[25,i].ForeText:=formatFloat('##0',_boxNum-_boxNum1);
grid1.cells[26,i].ForeText:=formatFloat('##0',_EmptyboxNum-_EmptyboxNum1);
grid1.cells[27,i].ForeText:=formatFloat('##0',_bottleNum-_bottleNum1);
end;
for i:=4 to grid1.RowCount-2 do
begin
grid1.cells[25,i].Color:=rgb(241,222,157);
grid1.cells[26,i].Color:=rgb(241,222,157);
grid1.cells[27,i].Color:=rgb(241,222,157);
if grid1.cells[25,i].ForeText<>'0' then
begin
grid1.cells[25,i].FontColor:=rgb(255,0,0);
end;
if grid1.cells[26,i].ForeText<>'0' then
begin
grid1.cells[26,i].FontColor:=rgb(255,0,0);
end;
if grid1.cells[27,i].ForeText<>'0' then
begin
grid1.cells[27,i].FontColor:=rgb(255,0,0);
end;
end;
{/*
计算最后一行累计
*/}
for i:=4 to grid1.RowCount-2 do
begin
if grid1.cells[25,i].foretext<>'' then
begin
_boxNumSum:=_boxNumSum+strToFloat(grid1.cells[25,i].foretext);
end;
if grid1.cells[25,i].foretext<>'' then
begin
_EmptyboxNumSum:=_boxNumSum+strToFloat(grid1.cells[25,i].foretext);
end;
if grid1.cells[27,i].foretext<>'' then
begin
_bottleNumSum:=_bottleNumSum+strToFloat(grid1.cells[27,i].foretext);
end;
end;
grid1.Cells[25,grid1.RowCount-1].ForeText:=formatFloat('##0',_boxNumSum);
grid1.Cells[26,grid1.RowCount-1].ForeText:=formatFloat('##0',_EmptyboxNumSum);
grid1.Cells[27,grid1.RowCount-1].ForeText:=formatFloat('##0',_bottleNumSum);
for i:=1 to grid1.ColCount-1 do
begin
grid1.Cells[i,grid1.rowcount-1].Color:=rgb(0,143,247);
grid1.Cells[i,grid1.rowcount-1].FontColor:=rgb(255,255,255);
end;
SELECT SUM(CONVERT(real, SW_bottleNum1) + CONVERT(real, SW_bottleNum2))
AS a
FROM Sale_WasteBill
WHERE (SW_ProduceName = '10度淡爽')⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -