int _fresh()
{
string sql, v0, v1, v2, v3, v4, v5, v6, v7, v8, v9, nDate;
int n, id, i, k;
db_run('drop table t_sTOP commit');
sql = "select h.* into t_sTOP from (select e.eba_name,c.voucher_no,c.note_info,a.tqty,a.prd_date,a.cmp_date,a.swh from (";
sql = sql + " select voucher_id,cpo=max(case when attr_id='CPO' then attr_val else '' end),";
sql = sql + " prd_date=max(case when attr_id='Prd_date' then attr_val else '' end),";
sql = sql + " cmp_date=max(case when attr_id='Cmp_date' then attr_val else '' end),";
sql = sql + " swh=max(case when attr_id='SWH' then attr_val else '' end),";
sql = sql + " tqty=max(case when attr_id='tQty' then attr_val else '' end) from ebs_v_attr a group by voucher_id ) a";
sql = sql + " inner join ebs_vr b on a.voucher_id=b.voucher_id inner join ebs_v c on a.voucher_id=c.voucher_id";
sql = sql + " inner join eba e on e.eba_id=b.eba_id) h";
db_run(sql + " commit");
db_run('drop table t_pSWH commit');
sql = "select v.* into t_pSWH from (";
sql = sql + "select b.eba_name,b.voucher_no,b.note_info,b.tqty,b.prd_date,b.cmp_date,";
sql = sql + "DateDiff(d,b.prd_date,convert(varchar(100),DATEADD(DAY,1,b.cmp_date),112)) tDays,";
sql = sql + "cast(b.swh AS decimal(9,4))*cast(b.tqty AS decimal(9,4)) tSWH,";
sql = sql + "cast(cast(b.swh AS decimal(9,4))*cast(b.tqty AS decimal(9,4))/";
sql = sql + "DateDiff(d,b.prd_date,convert(varchar(100),DATEADD(DAY,1,b.cmp_date),112)) as decimal(9,1)) reqSWH,";
sql = sql + "cast(c.wc/b.tqty*100 as decimal(9,2))eWC from t_sTOP b ";
sql = sql + "left join (select sum(a.inp_num)wc,left(a.ref_voucher_no,4) voucher_no from ebs_vr_item a ";
sql = sql + "where a.ref_voucher_type='HB' and a.sub_attr_val_1<>'-' group by left(a.ref_voucher_no,4))c on c.voucher_no=b.voucher_no";
sql = sql + ")v";
db_run(sql + " commit");
//分解排单
sql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_oSWH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)";
sql = sql + " drop table [dbo].[t_oSWH] ";
sql = sql + " CREATE TABLE [dbo].[t_oSWH] (";
sql = sql + " [eba_id] [varchar] (50) ,";
sql = sql + " [voucher_no] [varchar] (50) ,";
sql = sql + " [note_info] [varchar] (200) ,";
sql = sql + " [tQTY] [numeric](8, 0) ,";
sql = sql + " [tDays] [numeric](8, 0) ,";
sql = sql + " [eBegDate] [varchar] (20) ,";
sql = sql + " [eEndDate] [varchar] (20) ,";
sql = sql + " [eDay] [varchar] (20) ,";
sql = sql + " [tSWH] [numeric](8, 1) ,";
sql = sql + " [dSWH] [numeric](8, 1) ";
sql = sql + " ) ON [PRIMARY]";
db_run(sql);
int n, id, k, a, b;
string v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, sql;
db_select_area(0);
db_run('select * from t_pSWH');
n = db_row_count();
for (id = 0; id < n; id++)
{
db_select_area(0);
v1 = db_res(0);
v2 = db_res(1);
v3 = db_res(2);
v4 = db_res(3);
v5 = db_res(4);
v6 = db_res(5);
v7 = db_res(6);
v8 = db_res(7);
v9 = db_res(8);
v10 = db_res(9);
k = db_res(6);
for (a = 0; a < k; a++)
{
db_select_area(1);
sql = "insert into t_oSWH values('" + v1 + "','" + v2 + "','" + v3 + "','" + v4 + "','" + v7 ;
sql = sql + "','" + v5 + "','" + v6 + "','" + date_add(v5, a) + "','" + v8 + "','" + v9 + "')";
db_run(sql);
}
db_select_area(0);
db_next();
};
db_run('drop table t_tWH');
db_run("select eba_id+' '+voucher_no+' '+note_info nT,eDay,dSWH into t_tWH from t_oSWH");
db_run('drop table t_rWH');
sql = "declare @s nvarchar(4000)";
sql = sql + " set @s=''";
sql = sql + " Select @s=@s+','+quotename(nT)+'=sum(case when [nT]='+quotename(nT,'''')+' then dSWH else 0 end)'";
sql = sql + " from t_tWH group by nT";
sql = sql + " exec('select eDay'+@s+' into t_rWH from t_tWH group by eDay')";
db_run(sql);
sql = " select a.*,b.totalSWH from t_rWH a left join (";
sql = sql + " select eDay,sum(dSWH)totalSWH from t_nSWH group by eDay )b on a.eDay=b.eDay";
db_run(sql);
//创建汇总表
sql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_CFA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)";
sql = sql + " drop table [dbo].[t_CFA] ";
db_run(sql);
sql = "select c.* into t_CFA from (";
sql = sql + " select a.*,b.totalSWH from t_rWH a left join (select eDay,sum(dSWH)totalSWH from t_nSWH group by eDay )b on a.eDay=b.eDay ) c ";
db_run(sql);
sql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_xWH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)";
sql = sql + " drop table [dbo].[t_xWH] ";
sql = sql + " CREATE TABLE [dbo].[t_xWH] (";
sql = sql + " [eDay] [varchar] (50) ,";
sql = sql + " [avSWH] [varchar](20) ,";
sql = sql + " [tWorker] [varchar] (50) ,";
sql = sql + " [eWH] [varchar](20) ,";
sql = sql + " [COL] [varchar](20) ";
sql = sql + " ) ON [PRIMARY]";
db_run(sql);
sql = "insert into t_xWH select eday,'0','0','0','0' from t_CFA ";
db_run(sql);
sql = "drop table t_ccit commit";
db_run(sql);
sql = "select c.* into t_ccit from (";
sql = sql + "select a.*,b.avSWH,b.COL,b.tWorker,b.eWH from t_CFA a left join t_xWH b on a.eDay=b.eDay)c";
db_run(sql);
return 1;
}
int func_def_lmt()
{
_fresh()
def_lmt_date('beg_date', 'Qstart');
//lmt_set_must_checked('beg_date');
def_lmt_date('end_date', 'Qstop');
//lmt_set_must_checked('end_date');
def_lmt_dict("expType", "expType", 'whType');
lmt_set_must_checked('expType');
def_lmt_date("expDate", "expDate");
lmt_set_must_checked('expDate');
def_lmt_str('sWorker', 'Workers');
lmt_set_must_checked('sWorker')
def_lmt_str('sWH', 'WH@');
lmt_set_must_checked('sWH');
return 1;
};
int func_init_lmt_val()
{
def_lmt_set_val('sWorker', '0');
def_lmt_set_val('sWH', '0');
def_lmt_set_val('expType', 'O');
return 1;
};
int func_def_field()
{
string sql;
int a, b; db_select_area(0);
sql = "select * from t_ccit";
db_run(sql);
a = db_field_count();
for (b = 0; b < a; b++)
{
db_set_field_info(b, db_field_name(b), db_field_name(b), 80);
}
string sql2, logic, exDate;
num a, b, c;
a = lmt_val('sWorker');
b = lmt_val('sWH');
c = a * b;
logic = lmt_val('expType');
exDate = lmt_val('expDate');
if(lmt_checked('beg_date')==1 || lmt_checked('beg_date')==1)
{
sql2 = "select * from t_ccit where convert(varchar(100),eDay, 112) between '" + lmt_val('beg_date') + "' and '" + lmt_val('end_date') + "'";
}
else
{
sql2 = "select * from t_ccit";
}
if (logic != 'O' && (a == 0 || b == 0))
{
dbg('可用工人或单位工时为0');
sql = "update t_ccit set avSWH='0',COL='0',tWorker='0',eWH='0'";
db_run(sql);
db_run(sql2);
break;
}
else
{
switch (logic)
{
case "X":
sql = "update t_ccit set avSWH=" + c + ",COL=cast((" + c + "-totalSWH)/" + c + " as decimal(8,4))";
sql = sql + ",tWorker='" + a + "',eWH='" + b + "'";
db_run(sql);
break;
case "M":
sql = "update t_ccit set avSWH=" + c + ",COL=cast((" + c + "-totalSWH)/" + c + " as decimal(8,4))";
sql = sql + ",tWorker='" + a + "',eWH='" + b + "'";
sql = sql + " where left(eDay,6)='" + str_get_sub(exDate, 0, 6) + "'";
db_run(sql);
break;
case "D":
sql = "update t_ccit set avSWH=" + c + ",COL=cast((" + c + "-totalSWH)/" + c + " as decimal(8,4))";
sql = sql + ",tWorker='" + a + "',eWH='" + b + "'";
sql = sql + " where eDay='" + exDate + "'";
db_run(sql);
break;
case "O":
db_run(sql2);
break;
}
}
db_run(sql2);
return 1;
} |
