error.log

delphi 源码 小型企业管理软件

[2007-05-12 22:46:23]:
Query:
if object_id('gt')>0 select value from GT where name='DBVer' else select 0

[2007-05-12 22:46:24]:
SQL:

--更新模块表
if not exists(select 1 from GT where name='upModeTable' and value='2007-01-03')
begin
  delete from userPriv
  delete from modeTable

  insert into modeTable(formName,Caption) values('f1','基本资料管理')
  insert into modeTable(formName,Caption) values('f201','采购管理.基本资料.供应商表')
  insert into modeTable(formName,Caption) values('f202','采购管理.基本资料.料件单价表')
  insert into modeTable(formName,Caption) values('f203','采购管理.基本资料.付款条件表')
  insert into modeTable(formName,Caption) values('f221','采购管理.单据资料.采购总表')
  insert into modeTable(formName,Caption) values('f222','采购管理.单据资料.采购单')

  insert into modeTable(formName,Caption) values('f301','业务管理.基本资料.客户表')
  insert into modeTable(formName,Caption) values('f302','业务管理.基本资料.成品单价')
  insert into modeTable(formName,Caption) values('f321','业务管理.单据资料.订购单')
  insert into modeTable(formName,Caption) values('f341','业务管理.统计资料.对帐单')
  insert into modeTable(formName,Caption) values('f342','业务管理.统计资料.订单欠货情况表')

  insert into modeTable(formName,Caption) values('f3','统计查询')
  insert into modeTable(formName,Caption) values('f4','进出仓单管理')

  insert into GT(name,value) values('upModeTable','2007-01-03')
end

--更新供应商类别表
if not exists(select 1 from GT where name='upSupplierType' and value='2007-05-06')
begin
  delete from SupplierType

  insert into SupplierType(typeName) values('五金')
  insert into SupplierType(typeName) values('玻璃')
  insert into SupplierType(typeName) values('塑胶')
  insert into SupplierType(typeName) values('电器')
  insert into SupplierType(typeName) values('包装')

  insert into GT(name,value) values('upSupplierType','2007-05-06')
end

--更新货币表
if not exists(select 1 from GT where name='upHuobi' and value='2007-05-07')
begin
  delete from HD_HuoBi

  insert into HD_HuoBi (HBCode,HB01,HB02,HB03,HB04)values ('110','港币','HKD','',1);
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03,HB04)values ('116','日本元','JPY','',0.064);
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('121','澳门元','MOP','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('129','菲律宾比索','PHP','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('132','新加坡元','SGD','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('136','泰国铢','THB','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03,HB04)values ('142','人民币','CNY','',1);
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('300','欧元','EUR','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('302','丹麦克朗','DKK','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('303','英镑','GBP','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('326','挪威克朗','NOK','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('330','瑞典克朗','SEK','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('331','瑞士法郎','CHF','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('501','加拿大元','CAD','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03,HB04)values ('502','美元','USD','',7.83);
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('601','澳大利亚元','AUD','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('609','新西兰元','NZD','');

  insert into GT(name,value) values('upHuobi','2007-05-07')
end

--更新付款条件表
if not exists(select 1 from GT where name='upPayTerm' and value='2007-05-06')
begin
  delete from payTerm

  insert into payTerm (ptName,ptDelayDays)values ('现金',0);
  insert into payTerm (ptName,ptDelayDays)values ('月结30天',30);
  insert into payTerm (ptName,ptDelayDays)values ('月结60天',60);

  insert into GT(name,value) values('upPayTerm','2007-05-06')
end

if not exists(select 1 from GT where name='updateData' and value='2007-05-07')
begin
  delete from kh where khType<>0 and khType<>1

  delete from GT where name='sys_curr'
  insert into GT(name,value) values('sys_curr','人民币')

  insert into GT(name,value) values('updateData','2007-05-07')
end

--更新触发器
if not exists(select 1 from GT where name='upInvTrigger' and value='2007-05-10')
begin
  IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'trig_updateInv' AND type = 'TR')
  drop trigger trig_updateInv

  insert into GT(name,value) values('upInvTrigger','2007-05-10')
end

[2007-05-12 22:46:24]:
Query:
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'trig_updateInv' AND type = 'TR') select 1 else select 0

[2007-05-12 22:46:35]:
Query:
select 1 from users where uid='admin' and csum=Binary_Checksum('managernba')

[2007-05-12 22:46:35]:
Query:
select value from gt where name='GDecimalQty'

[2007-05-12 22:46:35]:
Query:
select value from gt where name='GDecimalPrice'

[2007-05-12 22:46:35]:
Query:
select value from gt where name='GDecimalAmt'

[2007-05-12 22:46:35]:
Query:
SELECT SERVERPROPERTY('MachineName')

[2007-05-12 22:46:35]:
Query:
SELECT SERVERPROPERTY('MachineName')

[2007-05-12 22:46:35]:
Query:
select value from gt where name='DBLastBack'


[2007-05-12 22:56:49]:
Query:
if object_id('gt')>0 select value from GT where name='DBVer' else select 0

[2007-05-12 22:56:49]:
SQL:

--更新模块表
if not exists(select 1 from GT where name='upModeTable' and value='2007-01-03')
begin
  delete from userPriv
  delete from modeTable

  insert into modeTable(formName,Caption) values('f1','基本资料管理')
  insert into modeTable(formName,Caption) values('f201','采购管理.基本资料.供应商表')
  insert into modeTable(formName,Caption) values('f202','采购管理.基本资料.料件单价表')
  insert into modeTable(formName,Caption) values('f203','采购管理.基本资料.付款条件表')
  insert into modeTable(formName,Caption) values('f221','采购管理.单据资料.采购总表')
  insert into modeTable(formName,Caption) values('f222','采购管理.单据资料.采购单')

  insert into modeTable(formName,Caption) values('f301','业务管理.基本资料.客户表')
  insert into modeTable(formName,Caption) values('f302','业务管理.基本资料.成品单价')
  insert into modeTable(formName,Caption) values('f321','业务管理.单据资料.订购单')
  insert into modeTable(formName,Caption) values('f341','业务管理.统计资料.对帐单')
  insert into modeTable(formName,Caption) values('f342','业务管理.统计资料.订单欠货情况表')

  insert into modeTable(formName,Caption) values('f3','统计查询')
  insert into modeTable(formName,Caption) values('f4','进出仓单管理')

  insert into GT(name,value) values('upModeTable','2007-01-03')
end

--更新供应商类别表
if not exists(select 1 from GT where name='upSupplierType' and value='2007-05-06')
begin
  delete from SupplierType

  insert into SupplierType(typeName) values('五金')
  insert into SupplierType(typeName) values('玻璃')
  insert into SupplierType(typeName) values('塑胶')
  insert into SupplierType(typeName) values('电器')
  insert into SupplierType(typeName) values('包装')

  insert into GT(name,value) values('upSupplierType','2007-05-06')
end

--更新货币表
if not exists(select 1 from GT where name='upHuobi' and value='2007-05-07')
begin
  delete from HD_HuoBi

  insert into HD_HuoBi (HBCode,HB01,HB02,HB03,HB04)values ('110','港币','HKD','',1);
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03,HB04)values ('116','日本元','JPY','',0.064);
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('121','澳门元','MOP','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('129','菲律宾比索','PHP','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('132','新加坡元','SGD','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('136','泰国铢','THB','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03,HB04)values ('142','人民币','CNY','',1);
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('300','欧元','EUR','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('302','丹麦克朗','DKK','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('303','英镑','GBP','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('326','挪威克朗','NOK','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('330','瑞典克朗','SEK','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('331','瑞士法郎','CHF','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('501','加拿大元','CAD','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03,HB04)values ('502','美元','USD','',7.83);
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('601','澳大利亚元','AUD','');
  insert into HD_HuoBi (HBCode,HB01,HB02,HB03)values ('609','新西兰元','NZD','');

  insert into GT(name,value) values('upHuobi','2007-05-07')
end

--更新付款条件表
if not exists(select 1 from GT where name='upPayTerm' and value='2007-05-06')
begin
  delete from payTerm

  insert into payTerm (ptName,ptDelayDays)values ('现金',0);
  insert into payTerm (ptName,ptDelayDays)values ('月结30天',30);
  insert into payTerm (ptName,ptDelayDays)values ('月结60天',60);

  insert into GT(name,value) values('upPayTerm','2007-05-06')
end

if not exists(select 1 from GT where name='updateData' and value='2007-05-07')
begin
  delete from kh where khType<>0 and khType<>1

  delete from GT where name='sys_curr'
  insert into GT(name,value) values('sys_curr','人民币')

  insert into GT(name,value) values('updateData','2007-05-07')
end

--更新触发器
if not exists(select 1 from GT where name='upInvTrigger' and value='2007-05-10')
begin
  IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'trig_updateInv' AND type = 'TR')
  drop trigger trig_updateInv

  insert into GT(name,value) values('upInvTrigger','2007-05-10')
end

[2007-05-12 22:56:49]:
Query:
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'trig_updateInv' AND type = 'TR') select 1 else select 0

[2007-05-12 22:56:50]:
Query:
select 1 from users where uid='admin' and csum=Binary_Checksum('managernba')

[2007-05-12 22:56:50]:
Query:
select value from gt where name='GDecimalQty'

[2007-05-12 22:56:50]:
Query:
select value from gt where name='GDecimalPrice'

[2007-05-12 22:56:50]:
Query:
select value from gt where name='GDecimalAmt'

[2007-05-12 22:56:50]:
Query:
SELECT SERVERPROPERTY('MachineName')

[2007-05-12 22:56:50]:
Query:
SELECT SERVERPROPERTY('MachineName')

[2007-05-12 22:56:50]:
Query:
select value from gt where name='DBLastBack'

[2007-05-12 22:58:37]:
Query:
select spunit from unit order by 1

[2007-05-12 23:10:15]:
Query:
select space(50),space(50) where 1=2
 union select 'khid','N009' from kh where khid='N009' and khid<>'009'
 union select 'khname','009' from kh where khname='009' and khname<>'009'

[2007-05-12 23:10:30]:
Query:
select ISNULL(max(cast(poid as integer)),0)+1 from poHead where isnumeric(poid)=1

[2007-05-12 23:10:30]:
Query:
select space(50),space(50) where 1=2
 union select 'poid','3' from poHead where poid='3' and poid<>''

[2007-05-12 23:10:39]:
Query:
select space(50),space(50) where 1=2
 union select 'poid','3' from poHead where poid='3' and poid<>'3'

[2007-05-12 23:10:51]:
Query:
select 1 from  mrpHead where poid='3' and status='批准'

[2007-05-12 23:10:51]:
SQL:
if object_id('tempdb..#temp1')>0 drop table #temp1
if object_id('tempdb..#temp2')>0 drop table #temp2
if object_id('tempdb..#temp3')>0 drop table #temp3

[2007-05-12 23:10:51]:
SQL:

  select *
  into #temp1
  from mrp
  where poid='3'

  select Qty=sum(b.Qty*a.Qty),b.ljno
  into #temp2
  from poBody a, BOM b
  where a.poid='3' and a.spno=b.spno
  group by b.ljno

  select top 0 *, qtyGap=qtyPO, InvQtyAvail=qtyPO
  into #temp3
  from #temp1

  insert into #temp3(poid, spno, qtyNeed, qtyPO, qtyLeft, qtyGap, InvQtyAvail)
  select '3',ljno,Qty,0,Qty,Qty,0
  from #temp2

  --先把库存数量抄过来
  update #temp3
  set InvQty=IsNULL(b.InvQty,0), InvQtyUsed=IsNULL(b.InvQtyUsed,0),
      InvQtyComing=IsNULL(b.InvQtyComing,0), khid=b.khid, price=b.spprice
  from #temp3 a, sp b
  where a.spno=b.spno

  --先考察库存可用量
  update #temp3 set InvQtyAvail=InvQty-InvQtyUsed

  update #temp3
  set qtyGap=0,InvQtyUsed=qtyNeed,InvQtyComing=0,qtyPO=0
  where InvQtyAvail>=qtyNeed

  update #temp3
  set qtyGap=qtyGap-InvQtyAvail,InvQtyUsed=InvQtyAvail
  where qtyGap>0

  --再考察已订购未回仓数量
  update #temp3
  set qtyGap=0,InvQtyComing=qtyGap,qtyPO=0
  where qtyGap>0 and InvQtyComing>=qtyGap

  update #temp3
  set qtyGap=qtyGap-InvQtyComing
  where qtyGap>0 and InvQtyComing<qtyGap

  --计算采购量(考虑最小订货量)
  update #temp3
  set qtyGap=0,qtyPO=qtyGap, amt=qtyGap*price
  where qtyGap>0

  update #temp3
  set qtyPO=b.moq, amt=b.moq*price
  from #temp3 a, sp b
  where a.spno=b.spno and a.qtyPO >0 and a.qtyPO < b.moq

  update #temp3
  set qtyLeft=QtyPO+InvQtyUsed+InvQtyComing - qtyNeed

  --修改商品库存量
  update sp
  set InvQtyUsed=IsNULL(a.InvQtyUsed,0)+b.InvQtyUsed
    , InvQtyComing=IsNULL(a.InvQtyComing,0)-b.InvQtyComing
  from sp a, #temp3 b
  where a.spno=b.spno

  --插入记录
  delete from mrpHead
  where poid='3'

  insert into mrpHead(poid, needDate, status)
  select  poid, needDate, '待批'
  from poHead
  where poid='3'

  delete from mrp
  where poid='3'

  insert into mrp(
    poid, spno, qtyNeed, qtyPO, qtyLeft, InvQty, InvQtyUsed, InvQtyComing, khid, price, amt)
  select
    poid, spno, qtyNeed, qtyPO, qtyLeft, InvQty, InvQtyUsed, InvQtyComing, khid, price, amt
  from #temp3

[2007-05-12 23:11:12]: