|
|
using NFine.Code;using NFine.Domain.Entity.SystemManage;using System.Collections.Generic;using System.Data;using System.Data.Common;using NFine.Repository;using System.Text;using System;using System.Configuration;using NFine.Data;using System.IO;using Newtonsoft.Json;using NFine.Domain._02_ViewModel;using NFine.Data.Extensions;using Newtonsoft.Json.Linq;
namespace NFine.Application.SRM{ public class WatchPanelApp : RepositoryFactory<ModuleEntity> { //到货明细
public DataTable GetList1(string queryJson, ref Pagination jqgridparam) { var queryParam = queryJson.ToJObject(); OperatorModel oo = NFine.Code.OperatorProvider.Provider.GetCurrent(); string workpoint = ConfigurationManager.ConnectionStrings["WorkPointCode"].ConnectionString; List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @"
select * from ( SELECT *,
收货数量 -已检数量 待检数量, CASE WHEN 收货数量 -已检数量> 0 THEN '待检验' WHEN 已检待入> 0 THEN '待入库' ELSE '完成' END 状态 FROM ( SELECT cc.receiptNo 到货单号,dd.orderno 订单号,dd.itemcode 料号,dd.planqty 收货数量,inv.invname 物料名称, isnull(( SELECT SUM (lotqty) FROM icsitemlot lot with (nolock) WHERE lot.TransNO=dd.ReceiptNO AND lot.TransLine=dd.ReceiptLine AND isnull(IQCStatus,'') !='' AND cc.workpoint=lot.workpoint),0) 已检数量, isnull(( SELECT SUM (lotqty) FROM icsitemlot lot with (nolock) WHERE lot.TransNO=dd.ReceiptNO AND lot.TransLine=dd.ReceiptLine AND isnull(IQCStatus,'') !='' AND cc.workpoint=lot.workpoint AND NOT EXISTS ( SELECT 1 FROM ICSWareHouseLotInfo info WHERE info.lotno=lot.lotno AND info.workpoint=lot.workpoint)),0) 已检待入 FROM ICSINVReceipt cc with (nolock) LEFT JOIN ICSINVReceiptDetail dd with (nolock) ON cc.ReceiptNO=dd.ReceiptNO AND dd.workpoint=cc.workpoint LEFT JOIN ICSINVENTORY inv with (nolock) ON inv.invcode=dd.itemcode AND dd.workpoint=inv.workpoint WHERE cc.mtime >=DATEADD(week, -1,getdate()) and cc.workpoint='" + workpoint + @"' ) ff ) gg ";
DataTable dt = Repository().FindTablePageBySql_Other(SqlText, "MESconnstr", parameter.ToArray(), ref jqgridparam);
return dt;
}
//来料不合格明细
public DataTable GetList2(string queryJson, ref Pagination jqgridparam) { var queryParam = queryJson.ToJObject(); string workpoint = ConfigurationManager.ConnectionStrings["WorkPointCode"].ConnectionString; OperatorModel oo = NFine.Code.OperatorProvider.Provider.GetCurrent(); List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @"
select * from ( SELECT cc.receiptNo 到货单,dd.orderno 采购订单号,dd.itemcode 料号, inv.invname 物料名称, sum(lot.lotqty) 不合格数量 ,kk.CKITEMDESC 不良原因 FROM ICSINVReceipt cc with (nolock) LEFT JOIN ICSINVReceiptDetail dd with (nolock) ON cc.ReceiptNO=dd.ReceiptNO AND dd.workpoint=cc.workpoint LEFT JOIN ICSINVENTORY inv with (nolock) ON inv.invcode=dd.itemcode AND dd.workpoint=inv.workpoint left join icsitemlot lot with (nolock) on lot.TransNO=dd.ReceiptNO AND lot.TransLine=dd.ReceiptLine AND cc.workpoint=lot.workpoint left join (select * from (select ROW_NUMBER()over(partition by LOTNO order by mtime desc,id ) rowId,lotno ,errdata , CASE WHEN NG='' THEN CKITEMDESC ELSE NG END CKITEMDESC from ICSIQCErrList ck with (nolock) where workpoint='" + workpoint + @"' and errdata='不合格' ) as AuctionRecords where rowId=1) kk on kk.lotno=lot.lotno WHERE 1=1 and cc.mtime >=DATEADD(MONTH, -1,getdate()) -- and kk.errdata='不合格' and isnull(lot.iqcstatus,'') ='不合格' and cc.workpoint='" + workpoint + @"' group by cc.receiptNo ,dd.orderno ,dd.itemcode , inv.invname ,kk.CKITEMDESC ) jj ";
DataTable dt = Repository().FindTablePageBySql_Other(SqlText, "MESconnstr", parameter.ToArray(), ref jqgridparam);
return dt;
}
//配料信息表
public DataTable GetList3(string queryJson, ref Pagination jqgridparam) { string workpoint = ConfigurationManager.ConnectionStrings["WorkPointCode"].ConnectionString; var queryParam = queryJson.ToJObject(); string type = queryParam["type"].ToString(); OperatorModel oo = NFine.Code.OperatorProvider.Provider.GetCurrent(); List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @" if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_TJRdTrans'))
drop table #Temp_TJRdTrans if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_TJRdTransLog')) drop table #Temp_TJRdTransLog if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_TJResult')) drop table #Temp_TJResult select tt.rdcode 出库单号,tt.invcode 产品编号, tt.rdstatus 业务类型, tt.depname 需求部门,tt.fromstoragecode 所属仓库, tt.mocode 生产工单号,'' 项目号,tt.rduser 制单人,tt.mtime 制单日期 , CASE WHEN isnull(tt.actqty,0) !=0 then '完成' else '待提交' end 状态, tt.rdNO,case when PLANQTY<0 then '物料退料' else '材料出库' end LogType into #Temp_TJRdTrans from ICSRdTransTer tt with (nolock) left join icsmo mo with (nolock) on mo.mocode=tt.mocode and mo.moseq=tt.moseq and mo.workpoint=tt.workpoint where tt.rdtype='材料出库单' AND TT.MTIME >DATEADD(MONTH, -1, GETDATE()) AND TT.WORKPOINT='" + workpoint + @"' and CASE WHEN isnull(tt.actqty,0) !=0 then '已完成' else '未完成' end ='" + type + @"'
SELECT * into #Temp_TJRdTransLog FROM ( select LOG.MUSERNAME,LOG.TransNO,LOG.TransLine,LOG.MTIME
,row_number() over (partition by LOG.BUSINESSCODE,LOG.TransNO,LOG.TransLine order by LOG.LOTNO desc,ID) rn
from ICSWareHouseLotInfoLog LOG with (nolock) WHERE EXISTS(SELECT 1 FROM #Temp_TJRdTrans TT WHERE tt.rdNO =LOG.TransLine AND TT.出库单号=LOG.TransNO AND LOG.BUSINESSCODE=tt.LogType and 状态='完成' ) AND LOG.WORKPOINT='" + workpoint + @"' ) FF WHERE RN=1
select distinct 出库单号,需求部门,生产工单号,制单人, CONVERT(varchar(19), 制单日期, 21) 制单日期,CONVERT(varchar(19), 完成日期, 21) 完成日期,状态 into #Temp_TJResult from ( SELECT tt.*,log.MUSERNAME 审核人,log.mtime 完成日期 FROM #Temp_TJRdTrans tt LEFT JOIN #Temp_TJRdTransLog log on tt.rdNO =LOG.TransLine AND TT.出库单号=LOG.TransNO
) gg ";
DataTable dt = Repository().FindTablePageBySql_OtherTemp(SqlText, " #Temp_TJResult ", @" if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_TJRdTrans'))
drop table #Temp_TJRdTrans if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_TJRdTransLog')) drop table #Temp_TJRdTransLog if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_TJResult')) drop table #Temp_TJResult ",
"MESconnstr", parameter.ToArray(), ref jqgridparam);
return dt;
}
//待入库明细
public DataTable GetList4(string queryJson, ref Pagination jqgridparam) { string workpoint = ConfigurationManager.ConnectionStrings["WorkPointCode"].ConnectionString; var queryParam = queryJson.ToJObject(); OperatorModel oo = NFine.Code.OperatorProvider.Provider.GetCurrent(); List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @"
select * from ( SELECT cc.receiptNo 到货单,dd.orderno 采购订单号,dd.itemcode 料号, inv.invname 物料名称, sum(lot.lotqty) 待入库数量 ,(select top 1 bincode from ICSWareHouseLotInfo info where info.lotno=lot.lotno and info.workpoint=cc.workpoint) 库位,lot.lotno 条码 FROM ICSINVReceipt cc with (nolock) LEFT JOIN ICSINVReceiptDetail dd with (nolock) ON cc.ReceiptNO=dd.ReceiptNO AND dd.workpoint=cc.workpoint LEFT JOIN ICSINVENTORY inv with (nolock) ON inv.invcode=dd.itemcode AND dd.workpoint=inv.workpoint left join icsitemlot lot with (nolock) on lot.TransNO=dd.ReceiptNO AND lot.TransLine=dd.ReceiptLine AND cc.workpoint=lot.workpoint left join ICSWareHouseLotInfolog kk with (nolock) on kk.lotno=lot.lotno and kk.workpoint=cc.workpoint and kk.transtype='收' WHERE 1=1 and kk.lotno is null and lot.IQCStatus= '合格' and cc.mtime >=DATEADD(month, -1,getdate()) and cc.workpoint='" + workpoint + @"' group by cc.receiptNo ,dd.orderno ,dd.itemcode , inv.invname ,lot.lotno ,kk.tostackcode,cc.workpoint ) jj ";
DataTable dt = Repository().FindTablePageBySql_Other(SqlText, "MESconnstr", parameter.ToArray(), ref jqgridparam);
return dt;
}
//生产进度汇总表
public DataTable GetList_WIPJinDu(string queryJson, string filters, ref Pagination jqgridparam) { try { List<DbParameter> parameter = new List<DbParameter>();
#region 过滤条件
string SQLWhere = "";
if (!queryJson.Contains("[]")) { JObject jobject = JObject.Parse(queryJson); JArray array = JArray.Parse(jobject["value"].ToString()); SQLWhere += " and A.SEGCODE in (";
foreach(JObject j in array) { SQLWhere += "'" + j["value"].ToString()+"',"; } SQLWhere= SQLWhere.TrimEnd(','); SQLWhere += ")"; }
#endregion
string SqlText = GetCaiJiSQL(SQLWhere);
DataTable dt = SqlHelper.GetDataTableBySql(SqlText); return dt;
} catch (Exception ex) { WriteLogFile(ex.ToString(), "看板异常"); } return null; }
public DataTable GetList_WIPJinDu(string value) { try { WriteLogFile("参数:"+ value, "看板异常"); List<DbParameter> parameter = new List<DbParameter>();
#region 过滤条件
string SQLWhere = ""; if (!string.IsNullOrEmpty(value)) { SQLWhere += " and A.SEGCODE in ('" + value .Replace(",","','")+ "')"; }
#endregion
string SqlText = GetCaiJiSQL(SQLWhere);
DataTable dt = SqlHelper.GetDataTableBySql(SqlText);
return dt;
} catch (Exception ex) { WriteLogFile(ex.ToString(), "看板异常"); } return null; }
private string GetCaiJiSQL(string SQLWhere) { string SqlText = @"
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMOSelect')) drop table #TempMOSelect if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempCaiJi')) drop table #TempCaiJi if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_MINDaJian')) drop table #Temp_MINDaJian select distinct lot.lotno,mo.mocode as 生产订单号, mo.moseq 生产订单行号, a.StartPlanDate as 工单日期,c.INVPARSETYPE as 产品代码, mo.ITEMCODE as 产品编码,mo.moplanqty as 计划量,lot.lotqty,a.mtime 换线时间,A.SEGCODE AS 线体 into #TempMOSelect from icsmo2user A with (nolock) inner JOIN ICSMO mo with (nolock) ON A.MOCODE=mo.MOCODE AND A.MOSEQ=mo.MOSEQ left join ICSITEMLot lot with (nolock) on mo.mocode=lot.transno and mo.moseq=lot.transline LEFT JOIN ICSINVENTORY C with (nolock) ON C.INVCODE=mo.ITEMCODE where mo.itemcode like '30%' and a.rcard='非批次' and a.StartPlanDate >= dateadd(day,-30,getdate()) " + SQLWhere + @"
select hh.lotno,hh.AB面,min(hh.mtime) mtime,tt.lotqty lotqty,生产订单号,生产订单行号 into #TempCaiJi from( select Rcard lotno,case when plane='BOTTOM' then 'B' else 'A' end AB面,min(mtime) mtime from ICSSZAOIDATA where Rcard is not null and Rcard!='' and Rcard in (select lotno from #TempMOSelect) group by Rcard,case when plane='BOTTOM' then 'B' else 'A' end union all select snno lotno,case when type='1' then 'A' else 'B' end AB面,min(data4) mtime from ICSAIODATA dd where snno is not null and snno!='' and snno in (select lotno from #TempMOSelect tt) group by snno ,case when type='1' then 'A' else 'B' end ) hh left join #TempMOSelect tt on hh.lotno=tt.lotno group by hh.lotno,hh.AB面,tt.lotqty,生产订单号,生产订单行号
select min(hh.mtime) mtime ,生产订单号 into #Temp_MINDaJian from ICSSMTFEEDINGINFO hh inner join #TempCaiJi tt on hh.mocode=tt.生产订单号 group by 生产订单号
select * , case when 计划量 is not null and 计划量!=0 then cast(Convert(decimal(18,2),产出量*1.0000/计划量*100) as nvarchar) +'%' else '0%' end 完成率 from ( select gg.线体,gg.生产订单号 ,gg.AB面,gg.工单日期 ,gg.产品编码 ,gg.产品代码 ,gg.换线时间 ,cc2.mtime 打件时间, (select min(mtime) from #TempCaiJi cc where cc.生产订单号=gg.生产订单号 and cc.生产订单行号=gg.生产订单行号 and cc.AB面=gg.AB面 ) 首检时间, gg.计划量, (select sum(lotqty) from #TempCaiJi cc where cc.生产订单号=gg.生产订单号 and cc.生产订单行号=gg.生产订单行号 and cc.AB面=gg.AB面 ) 产出量
from ( select cc.AB面,mm.生产订单号, mm.生产订单行号, mm.工单日期,mm.产品代码, mm. 产品编码,mm.计划量,min(换线时间) 换线时间,mm.线体 from #TempCaiJi cc left join #TempMOSelect mm with (nolock) on cc.lotno=mm.lotno group by cc.AB面,mm.生产订单号, mm.生产订单行号, mm.工单日期,mm.产品代码, mm. 产品编码,mm.计划量,mm.线体 ) gg left join #Temp_MINDaJian cc2 on cc2.生产订单号=gg.生产订单号 ) jj order by 2,1,3 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMOSelect')) drop table #TempMOSelect if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempCaiJi')) drop table #TempCaiJi if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_MINDaJian')) drop table #Temp_MINDaJian
";
return SqlText; }
//项目阶段
public DataTable GetProjectJD() { List<DbParameter> parameter = new List<DbParameter>(); string sql = @" select distinct REPLACE(PROJECTSTAGE, '生产确认表', '待装配') NAME from EXP_PROJECTPRODUCTIONSCHEDULE
where OQC is null and not (PROJECTID>='2117101' and PROJECTID<='2117116') and REPLACE(PROJECTSTAGE, '生产确认表', '待装配') is not null";
DataTable dt = OracleHelper.GetTable(sql, "Oracleconnstr", parameter.ToArray()); return dt; //DataTable tblDatas = new DataTable("Datas");
//tblDatas.Columns.Add("NAME", Type.GetType("System.String"));
//for (int i = 0; i < 5; i++)
//{
// DataRow newRow;
// newRow = tblDatas.NewRow();
// newRow["NAME"] = i.ToString();
// tblDatas.Rows.Add(newRow);
//}
//return tblDatas;
}
//设备类型
public DataTable GetEQPType() { List<DbParameter> parameter = new List<DbParameter>(); string sql = @" select distinct DEVICETYPE NAME from EXP_PROJECTPRODUCTIONSCHEDULE
where OQC is null and not (PROJECTID>='2117101' and PROJECTID<='2117116') and DEVICETYPE is not null ";
DataTable dt = OracleHelper.GetTable(sql, "Oracleconnstr", parameter.ToArray()); return dt; }
//状态
public DataTable GetStatus() { List<DbParameter> parameter = new List<DbParameter>(); string sql = @" select distinct case when PROJECTSTAGE ='FAT完成' then '完成' when PROJECTSTAGE ='FQC' then '完成'
when BEGINDATE like '%-%-%' and TO_DATE(BEGINDATE,'YYYY-MM-DD')-sysdate < 7 and NVL(RTRIM(PROJECTSCHEDULE,'%'),0) <80 then '紧急' else STATUS end NAME from EXP_PROJECTPRODUCTIONSCHEDULE where OQC is null and not (PROJECTID>='2117101' and PROJECTID<='2117116') and case when PROJECTSTAGE ='FAT完成' then '完成' when PROJECTSTAGE ='FQC' then '完成' when BEGINDATE like '%-%-%' and TO_DATE(BEGINDATE,'YYYY-MM-DD')-sysdate < 7 and NVL(RTRIM(PROJECTSCHEDULE,'%'),0) <80 then '紧急' else STATUS end is not null ";
DataTable dt = OracleHelper.GetTable(sql, "Oracleconnstr", parameter.ToArray()); return dt; }
//生产进度-层析柱Qty
public DataTable GetWIPQty1() { try { List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @"
SELECT sum(case when REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ) like '%开始%' or REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ) not like '%完成%' then to_number ( QUANTITY ) else 0 end ) WIPQTY, sum(case when REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ) not like '%开始%' and REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ) like '%完成%' then to_number ( QUANTITY ) else 0 end ) COMQTY, sum(to_number ( QUANTITY ) ) ZQTY, REPLACE( REPLACE( REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ), '完成', '' ), '开始', '' ) PROJECTStr FROM EXP_PROJECTPRODUCTIONSCHEDULE WHERE 1 = 1 and not (PROJECTID>='2117101' and PROJECTID<='2117116') and DEVICETYPE in ('EAC系列') and OQC is null GROUP BY REPLACE( REPLACE( REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ), '完成', '' ), '开始', '' ) HAVING sum(to_number ( QUANTITY ) )>0 ";
DataTable dt = OracleHelper.GetTable(SqlText, "Oracleconnstr", parameter.ToArray());
return dt; } catch (Exception ex) { WriteLogFile(ex.ToString(), "看板异常"); } return null; }
//生产进度-设备Qty
public DataTable GetWIPQty5() { try { List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @" SELECT
sum(case when REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ) like '%开始%' or REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ) not like '%完成%' then to_number ( QUANTITY ) else 0 end ) WIPQTY, sum(case when REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ) not like '%开始%' and REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ) like '%完成%' then to_number ( QUANTITY ) else 0 end ) COMQTY, sum(to_number ( QUANTITY ) ) ZQTY, REPLACE( REPLACE( REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ), '完成', '' ), '开始', '' ) PROJECTStr FROM EXP_PROJECTPRODUCTIONSCHEDULE WHERE 1 = 1 AND NOT ( PROJECTID >= '2117101' AND PROJECTID <= '2117116' ) AND DEVICETYPE NOT IN ( 'SAC系列','EAC系列' ) AND OQC IS NULL GROUP BY REPLACE( REPLACE( REPLACE( PROJECTSTAGE, '生产确认表', '待装配' ), '完成', '' ), '开始', '' ) HAVING sum(to_number ( QUANTITY ) )>0 ";
DataTable dt = OracleHelper.GetTable(SqlText, "Oracleconnstr", parameter.ToArray());
return dt; } catch (Exception ex) { WriteLogFile(ex.ToString(), "看板异常"); } return null; }
//生产进度 柱状图 （在制数 完成数 ）
public DataTable GetWIPQty2() {
try { //生产确认表 有值中： 电装开始 有值 就是已开工，电装开始 无值 就是待开工
List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @" SELECT
SUM(CASE WHEN PRODUCTIONCONFIRM IS NOT NULL AND ELECTRICASSEMBLYSTART IS NOT NULL THEN to_number(QUANTITY) ELSE 0 END) YKGQty, SUM(CASE WHEN PRODUCTIONCONFIRM IS NOT NULL AND ELECTRICASSEMBLYSTART IS NULL THEN to_number(QUANTITY) ELSE 0 END) DKGQty
from EXP_PROJECTPRODUCTIONSCHEDULE WHERE 1=1 AND PRODUCTIONCONFIRM IS NOT NULL and not (PROJECTID>='2117101' and PROJECTID<='2117116') and OQC is null ";
DataTable dt = OracleHelper.GetTable(SqlText, "Oracleconnstr", parameter.ToArray());
return dt; } catch (Exception ex) { WriteLogFile(ex.ToString(), "看板异常"); } return null; }
//设备类型在制数
public DataTable GetWIPQty3() {
try { //生产确认表 有值 FQC 无值
List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @" SELECT
SUM( to_number(QUANTITY) ) Qty,nvl(DEVICETYPE,'空设备类型') EQPTypeName from EXP_PROJECTPRODUCTIONSCHEDULE WHERE 1=1 AND PRODUCTIONCONFIRM IS NOT NULL and not (PROJECTID>='2117101' and PROJECTID<='2117116') AND OQC IS NULL GROUP BY DEVICETYPE ";
DataTable dt = OracleHelper.GetTable(SqlText, "Oracleconnstr", parameter.ToArray());
return dt; } catch (Exception ex) { WriteLogFile(ex.ToString(), "看板异常"); } return null; }
//待开工项目（Qty）
public DataTable GetWIPQty4() {
try { //生产确认表 有值 电装开始 无值
List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @"
SELECT SUM ( to_number ( QUANTITY ) ) Qty, SUBSTR ( BEGINDATE, 0, 4+instr ( SUBSTR ( BEGINDATE, 6, 3 ), '-' ) ) MonthStr FROM EXP_PROJECTPRODUCTIONSCHEDULE WHERE 1 = 1 AND( (ELECTRICASSEMBLYSTART IS NULL AND PRODUCTIONCONFIRM IS NOT NULL and OQC IS NULL ) or PROJECTSTAGE='生产确认表' ) AND BEGINDATE IS NOT NULL and not (PROJECTID>='2117101' and PROJECTID<='2117116') GROUP BY SUBSTR ( BEGINDATE, 0, 4+instr ( SUBSTR ( BEGINDATE, 6, 3 ), '-' ) ), SUBSTR ( BEGINDATE, 0, 4 ), to_number ( SUBSTR ( BEGINDATE, 6, instr ( SUBSTR ( BEGINDATE, 6, 3 ), '-' ) - 1 ) ) ORDER BY SUBSTR ( BEGINDATE, 0, 4 ), to_number ( SUBSTR ( BEGINDATE, 6, instr ( SUBSTR ( BEGINDATE, 6, 3 ), '-' ) - 1 ) ) ";
DataTable dt = OracleHelper.GetTable(SqlText, "Oracleconnstr", parameter.ToArray());
return dt; } catch (Exception ex) { WriteLogFile(ex.ToString(), "看板异常"); } return null; }
//收货进度
public DataTable GetRCVJingDu() { string workpoint = ConfigurationManager.ConnectionStrings["WorkPointCode"].ConnectionString; List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @"
if exists(select* from tempdb..sysobjects where id= object_id('tempdb..#TEMP_ItemCK')) drop table #TEMP_ItemCK
select sum(lotqty) Qty,cktype INTO #TEMP_ItemCK from( select lot.lotno, lot.lotqty, lot.IQCStatus, lot.step, case when lot.step= '优先' and lot.IQCStatus= '' then '优先待检' when isnull(lot.step,'')!='优先' and lot.IQCStatus='' then '待检' when lot.IQCStatus= '合格' then '已检待入' else '' end CKType from ICSINVReceipt cc with (nolock) left join ICSINVReceiptDetail dd with (nolock) on cc.ReceiptNO= dd.ReceiptNO and dd.workpoint= cc.workpoint left join ICSITEMLot lot with (nolock) on lot.mcode= dd.itemcode and lot.TransNO= dd.ReceiptNO and lot.TransLine= dd.ReceiptLine and dd.workpoint= lot.workpoint
where not exists(select 1 from ICSWareHouseLotInfoLog log with (nolock) where log.lotno= lot.lotno) and lot.lotno is not null and cc.workpoint ='" + workpoint + @"' and cc.mtime >=DATEADD(week, -1,getdate()) ) gg where cktype!='' group by cktype
select * from ( SELECT QTY,CKTYPE,cast(CONVERT(DECIMAL(18,2),QTY/(SELECT SUM(QTY) FROM #TEMP_ItemCK )*100) as NVARCHAR) +'%' Qtypercent from #TEMP_ItemCK union all SELECT 0,'优先待检', '0%' Qtypercent where not EXISTS(select 1 from #TEMP_ItemCK where cktype='优先待检') union all SELECT 0,'已检待入', '0%' Qtypercent where not EXISTS(select 1 from #TEMP_ItemCK where cktype='已检待入') union all SELECT 0,'待检', '0%' Qtypercent where not EXISTS(select 1 from #TEMP_ItemCK where cktype='待检') ) ff order by case CKTYPE when '已检待入' then 0 when '优先待检' then 1 when '待检' then 2 else 3 end ";
DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "MESconnstr", null);
return dt; }
//到货数量(item)
public DataTable GetRCVQty(string type) { string workpoint = ConfigurationManager.ConnectionStrings["WorkPointCode"].ConnectionString; List<DbParameter> parameter = new List<DbParameter>();
string SqlText = ""; switch (type) { case "日": SqlText = @"
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2 SELECT CONVERT( VARCHAR ( 100 ), DATEADD( DAY, number *- 1, getdate( ) ), 23 ) AS 日期 INTO #TEMP_Itemrr_tt2 FROM master.dbo.spt_values WHERE type = 'p' AND number <= 6 AND number >= 0 SELECT SUM( planqty ) QTY, CONVERT ( VARCHAR ( 100 ), mtime, 23 ) 日期 INTO #TEMP_Itemrr_tt FROM ICSINVReceiptDetail WHERE mtime >= CONVERT ( VARCHAR ( 10 ), DATEADD( DAY, - 6, getdate( ) ), 23 ) + ' 00:00:00' AND workpoint = '" + workpoint + @"' GROUP BY CONVERT ( VARCHAR ( 100 ), mtime, 23 ) SELECT substring( aa.日期,6,5) 日期, isnull( bb.qty, 0 ) QTY FROM #TEMP_Itemrr_tt2 aa LEFT JOIN #TEMP_Itemrr_tt bb ON aa.日期 = bb.日期 ORDER BY 1 IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2 ";
break; case "周": SqlText = @"
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2
SELECT DATEPART(wk,CONVERT( VARCHAR ( 10 ), DATEADD( week, -1*NUMBER, getdate( ) ),23)+' 00:00:00') AS 日期 , CONVERT( VARCHAR ( 4 ), DATEADD( week, -1*NUMBER, getdate( ) ),23) 年 INTO #TEMP_Itemrr_tt2 FROM master.dbo.spt_values WHERE type = 'p' AND number <= 7 AND number >= 0 SELECT SUM( planqty ) QTY, DATEPART(wk, mtime) 日期,CONVERT( VARCHAR ( 4 ),mtime,23) 年 INTO #TEMP_Itemrr_tt FROM ICSINVReceiptDetail with (nolock) WHERE mtime >= CONVERT( VARCHAR ( 10 ), DATEADD( week, -7, getdate( ) ),23)+' 00:00:00' AND workpoint = '" + workpoint + @"' GROUP BY DATEPART(wk, mtime) ,CONVERT( VARCHAR ( 4 ),mtime,23) ORDER BY CONVERT( VARCHAR ( 4 ),mtime,23),DATEPART(wk, mtime)
SELECT aa.日期, isnull( bb.qty, 0 ) QTY FROM #TEMP_Itemrr_tt2 aa LEFT JOIN #TEMP_Itemrr_tt bb ON aa.日期 = bb.日期 AND aa.年 = bb.年 ORDER BY aa.年, aa.日期 IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2 "; break;
case "月": SqlText = @"
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2
SELECT DATEPART(month,CONVERT( VARCHAR ( 10 ), DATEADD( month, -1*NUMBER, getdate( ) ),23)+' 00:00:00') AS 日期 , CONVERT( VARCHAR ( 4 ), DATEADD( month, -1*NUMBER, getdate( ) ),23) 年 INTO #TEMP_Itemrr_tt2 FROM master.dbo.spt_values WHERE type = 'p' AND number <= 11 AND number >= 0 SELECT SUM( planqty ) QTY, DATEPART(month, mtime) 日期,CONVERT( VARCHAR ( 4 ),mtime,23) 年 INTO #TEMP_Itemrr_tt FROM ICSINVReceiptDetail with (nolock) WHERE mtime >= CONVERT( VARCHAR ( 10 ), DATEADD( month, -11, getdate( ) ),23)+' 00:00:00' AND workpoint = '" + workpoint + @"' GROUP BY DATEPART(month, mtime) ,CONVERT( VARCHAR ( 4 ),mtime,23) ORDER BY CONVERT( VARCHAR ( 4 ),mtime,23),DATEPART(month, mtime)
SELECT aa.日期, isnull( bb.qty, 0 ) QTY FROM #TEMP_Itemrr_tt2 aa LEFT JOIN #TEMP_Itemrr_tt bb ON aa.日期 = bb.日期 AND aa.年 = bb.年 ORDER BY aa.年, aa.日期 IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2 "; break;
case "年": SqlText = @"
SELECT SUM( planqty ) QTY, CONVERT( VARCHAR ( 4 ),mtime,23) 日期 FROM ICSINVReceiptDetail with (nolock) WHERE workpoint = '" + workpoint + @"' GROUP BY CONVERT( VARCHAR ( 4 ),mtime,23) ORDER BY CONVERT( VARCHAR ( 4 ),mtime,23)
"; break;
}
DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "MESconnstr", null);
return dt; }
//入库数量(item)
public DataTable GetWareInQty(string type) { string workpoint = ConfigurationManager.ConnectionStrings["WorkPointCode"].ConnectionString; List<DbParameter> parameter = new List<DbParameter>();
string SqlText = ""; switch (type) { case "日": SqlText = @"
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2 SELECT CONVERT( VARCHAR ( 100 ), DATEADD( DAY, number *- 1, getdate( ) ), 23 ) AS 日期 INTO #TEMP_Itemrr_tt2 FROM master.dbo.spt_values WHERE type = 'p' AND number <= 6 AND number >= 0 SELECT SUM( TRANSQTY ) QTY, CONVERT ( VARCHAR ( 100 ), MTIME, 23 ) 日期 INTO #TEMP_Itemrr_tt FROM ICSWareHouseLotInfoLOG log with (nolock) WHERE MTIME >= CONVERT ( VARCHAR ( 10 ), DATEADD( DAY, - 6, getdate( ) ), 23 ) + ' 00:00:00' AND workpoint = '" + workpoint + @"' and log.transtype in ('收','退') GROUP BY CONVERT ( VARCHAR ( 100 ), MTIME, 23 ) SELECT substring( aa.日期,6,5) 日期, isnull( bb.qty, 0 ) QTY FROM #TEMP_Itemrr_tt2 aa LEFT JOIN #TEMP_Itemrr_tt bb ON aa.日期 = bb.日期 ORDER BY 1 IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2 ";
break; case "周": SqlText = @"
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2
SELECT DATEPART(wk,CONVERT( VARCHAR ( 10 ), DATEADD( week, -1*NUMBER, getdate( ) ),23)+' 00:00:00') AS 日期 , CONVERT( VARCHAR ( 4 ), DATEADD( week, -1*NUMBER, getdate( ) ),23) 年 INTO #TEMP_Itemrr_tt2 FROM master.dbo.spt_values WHERE type = 'p' AND number <= 7 AND number >= 0 SELECT SUM( transqty ) QTY, DATEPART(wk, mtime) 日期,CONVERT( VARCHAR ( 4 ),mtime,23) 年 INTO #TEMP_Itemrr_tt FROM ICSWareHouseLotInfolog log with (nolock) WHERE mtime >= CONVERT( VARCHAR ( 10 ), DATEADD( week, -7, getdate( ) ),23)+' 00:00:00' AND workpoint = '" + workpoint + @"' and log.transtype in ('收','退') GROUP BY DATEPART(wk, mtime) ,CONVERT( VARCHAR ( 4 ),mtime,23) ORDER BY CONVERT( VARCHAR ( 4 ),mtime,23),DATEPART(wk, mtime)
SELECT aa.日期, isnull( bb.qty, 0 ) QTY FROM #TEMP_Itemrr_tt2 aa LEFT JOIN #TEMP_Itemrr_tt bb ON aa.日期 = bb.日期 AND aa.年 = bb.年 ORDER BY aa.年, aa.日期 IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2 "; break;
case "月": SqlText = @"
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2
SELECT DATEPART(month,CONVERT( VARCHAR ( 10 ), DATEADD( month, -1*NUMBER, getdate( ) ),23)+' 00:00:00') AS 日期 , CONVERT( VARCHAR ( 4 ), DATEADD( month, -1*NUMBER, getdate( ) ),23) 年 INTO #TEMP_Itemrr_tt2 FROM master.dbo.spt_values WHERE type = 'p' AND number <= 11 AND number >= 0 SELECT SUM( transqty ) QTY, DATEPART(month, mtime) 日期,CONVERT( VARCHAR ( 4 ),mtime,23) 年 INTO #TEMP_Itemrr_tt FROM ICSWareHouseLotInfolog log with (nolock) WHERE mtime >= CONVERT( VARCHAR ( 10 ), DATEADD( month, -11, getdate( ) ),23)+' 00:00:00' AND workpoint = '" + workpoint + @"' and log.transtype in ('收','退') GROUP BY DATEPART(month, mtime) ,CONVERT( VARCHAR ( 4 ),mtime,23) ORDER BY CONVERT( VARCHAR ( 4 ),mtime,23),DATEPART(month, mtime)
SELECT aa.日期, isnull( bb.qty, 0 ) QTY FROM #TEMP_Itemrr_tt2 aa LEFT JOIN #TEMP_Itemrr_tt bb ON aa.日期 = bb.日期 AND aa.年 = bb.年 ORDER BY aa.年, aa.日期 IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt' ) ) DROP TABLE #TEMP_Itemrr_tt
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#TEMP_Itemrr_tt2' ) ) DROP TABLE #TEMP_Itemrr_tt2 "; break;
case "年": SqlText = @"
SELECT SUM( transqty ) QTY, CONVERT( VARCHAR ( 4 ),mtime,23) 日期 FROM ICSWareHouseLotInfolog log with (nolock) WHERE workpoint = '" + workpoint + @"' and log.transtype in ('收','退') GROUP BY CONVERT( VARCHAR ( 4 ),mtime,23) ORDER BY CONVERT( VARCHAR ( 4 ),mtime,23)
"; break;
}
DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "MESconnstr", null);
return dt; }
//配料进度
public DataTable GetPeiLiaoJingDu() { string workpoint = ConfigurationManager.ConnectionStrings["WorkPointCode"].ConnectionString; List<DbParameter> parameter = new List<DbParameter>(); string SqlText = @"
select UnDoQty, DoneQty, case when DoneQty + UnDoQty=0 then '0%' else cast(Convert(decimal(18, 2), DoneQty / (DoneQty + UnDoQty) * 100) as NVARCHAR) + '%' end DoneQtyPer, case when DoneQty + UnDoQty=0 then '0%' else cast(Convert(decimal(18, 2), UnDoQty / (DoneQty + UnDoQty) * 100) as NVARCHAR) + '%' end UnDoQtyPer from( select isnull(sum(case when isnull(actqty, 0) = 0 then planqty else 0 end),0) UnDoQty, isnull(sum(case when isnull(actqty, 0) = 0 then 0 else actqty end),0) DoneQty from ICSRdTransTer with (nolock) where RdType = '材料出库单' and planqty > 0 and workpoint ='" + workpoint + @"' and mtime >=DATEADD(MONTH, -1,getdate()) ) gg ";
DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "MESconnstr", null); DataTable retdd = new DataTable(); retdd.Columns.Add("cktype", Type.GetType("System.String")); retdd.Columns.Add("Qty", Type.GetType("System.Decimal")); retdd.Columns.Add("Qtypercent", Type.GetType("System.String"));
if (dt != null && dt.Rows.Count > 0) { DataRow newRow = retdd.NewRow(); newRow["cktype"] = "待配料"; newRow["Qty"] = Convert.ToDecimal(dt.Rows[0]["UnDoQty"].ToString()); newRow["Qtypercent"] = dt.Rows[0]["UnDoQtyPer"].ToString(); retdd.Rows.Add(newRow);
DataRow newRow2 = retdd.NewRow(); newRow2["cktype"] = "已配料"; newRow2["Qty"] = Convert.ToDecimal(dt.Rows[0]["DoneQty"].ToString()); newRow2["Qtypercent"] = dt.Rows[0]["DoneQtyPer"].ToString(); retdd.Rows.Add(newRow2); } else { DataRow newRow = retdd.NewRow(); newRow["cktype"] = "待配料"; newRow["Qty"] = 0; newRow["Qtypercent"] = "0%"; retdd.Rows.Add(newRow);
DataRow newRow2 = retdd.NewRow(); newRow2["cktype"] = "已配料"; newRow2["Qty"] = 0; newRow2["Qtypercent"] = "0%"; retdd.Rows.Add(newRow2); }
return retdd; }
public DataTable GetSScode(string cookie) { string sql = @"
select distinct a.sscode as name,a.sscode as value,'' as selected from ICSSS as a,ICSSEG as b,ICSSHIFTTYPE as c where a.SEGID = b.ID and a.SHIFTTYPEID = c.ID and b.WorkPoint = a.WorkPoint and c.WorkPoint = a.WorkPoint order by 1 ";
DataTable table= Repository().GetDataTableBySql_Other(sql, "MESconnstr", null); foreach (DataRow row in table.Rows) { if (!string.IsNullOrEmpty(cookie)) { JObject jobject = JObject.Parse(cookie); JArray array = JArray.Parse(jobject["value"].ToString()); foreach (JObject j in array) { if (j["value"].ToString() == row["value"].ToString()) row["selected"] = "true"; }
}
} table.AcceptChanges(); return table; }
public static void WriteLogFile(string input, string txtName) { try { string logAdress = "C:\\看板日志" + "\\"; if (!System.IO.Directory.Exists(logAdress)) { System.IO.Directory.CreateDirectory(logAdress);//不存在就创建目录
}
string adress = logAdress + txtName; if (!System.IO.Directory.Exists(adress)) { System.IO.Directory.CreateDirectory(adress);//不存在就创建目录
}
// string logAdress = ConfigurationManager.AppSettings["logAdress"].ToString();
/**/ ///指定日志文件的目录
string fname = adress + "\\" + "log" + DateTime.Now.ToString("yy-MM-dd") + ".txt"; /**/ ///定义文件信息对象
FileInfo finfo = new FileInfo(fname);
if (!finfo.Exists) { FileStream fs; fs = File.Create(fname); fs.Close(); finfo = new FileInfo(fname); }
/**/ ///判断文件是否存在以及是否大于2K
if (finfo.Length > 1024 * 1024 * 10) { /**/ ///文件超过10MB则重命名
File.Move(logAdress + "\\Log\\" + txtName + ".txt", Directory.GetCurrentDirectory() + DateTime.Now.TimeOfDay + "\\Log\\" + txtName + ".txt"); /**/ ///删除该文件
//finfo.Delete();
} //finfo.AppendText();
/**/ ///创建只写文件流
using (FileStream fs = finfo.OpenWrite()) { /**/ ///根据上面创建的文件流创建写数据流
StreamWriter w = new StreamWriter(fs);
/**/ ///设置写数据流的起始位置为文件流的末尾
///设置写数据流的起始位置为文件流的末尾
w.BaseStream.Seek(0, SeekOrigin.End);
w.WriteLine("*****************Start*****************"); w.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); /**/ ///写入当前系统时间并换行
/**/ ///写入日志内容并换行
w.WriteLine(input);
/**/ ///写入------------------------------------“并换行
w.WriteLine("------------------END------------------------");
/**/ ///清空缓冲区内容，并把缓冲区内容写入基础流
w.Flush();
/**/ ///关闭写数据流
w.Close(); } } catch (Exception ex) { }
}
public string ConvertDataTableToJson(DataTable dt) { StringBuilder sbs = new StringBuilder(); if (dt.Rows.Count > 0)//如果有记录
{ sbs.Append("{'" + dt.TableName + "':["); string str = ""; foreach (DataRow dr in dt.Rows)//开始拼
{ string result = ""; foreach (DataColumn dc in dt.Columns) { result += string.Format(",'{0}':'{1}'", dc.ColumnName, dr[dc.ColumnName]); } result = result.Substring(1); result = ",{" + result + "}"; str += result; } str = str.Substring(1); sbs.Append(str); sbs.Append("]}"); } else//如果没有记录
{ sbs.Append(""); } return sbs.ToString(); }
#region 执行大看板
public DataTable GetZhiXing_MO(string ItemNumber) { string where1 = ""; string SqlText = ""; if (ItemNumber == "30") { SqlText = @" if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMOSelect2'))
drop table #TempMOSelect2 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempCaiJi2')) drop table #TempCaiJi2 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_MINDaJian2')) drop table #Temp_MINDaJian2 select distinct lot.lotno,mo.mocode as 生产订单号, mo.moseq 生产订单行号, c.INVPARSETYPE as 产品代码,mo.moplanenddate 计划完工日期, mo.ITEMCODE as 产品编码,mo.moplanqty as 计划量,lot.lotqty, A.SEGCODE AS 线体 ,c.invname 产品名称 into #TempMOSelect2 from icsmo2user A with (nolock) inner JOIN ICSMO mo with (nolock) ON A.MOCODE=mo.MOCODE AND A.MOSEQ=mo.MOSEQ left join ICSITEMLot lot with (nolock) on mo.mocode=lot.transno and mo.moseq=lot.transline LEFT JOIN ICSINVENTORY C with (nolock) ON C.INVCODE=mo.ITEMCODE where mo.itemcode like '30%' and a.rcard='非批次' and a.StartPlanDate >= dateadd(day,-30,getdate())
select hh.lotno,hh.AB面,min(hh.mtime) mtime,tt.lotqty lotqty,生产订单号,生产订单行号 into #TempCaiJi2 from( select Rcard lotno,case when plane='BOTTOM' then 'B' else 'A' end AB面,min(mtime) mtime from ICSSZAOIDATA where Rcard is not null and Rcard!='' and Rcard in (select lotno from #TempMOSelect2) group by Rcard,case when plane='BOTTOM' then 'B' else 'A' end union all select snno lotno,case when type='1' then 'A' else 'B' end AB面,min(data4) mtime from ICSAIODATA dd where snno is not null and snno!='' and snno in (select lotno from #TempMOSelect2 tt) group by snno ,case when type='1' then 'A' else 'B' end ) hh left join #TempMOSelect2 tt on hh.lotno=tt.lotno group by hh.lotno,hh.AB面,tt.lotqty,生产订单号,生产订单行号
select min(hh.mtime) mtime ,生产订单号 into #Temp_MINDaJian2 from ICSSMTFEEDINGINFO hh inner join #TempCaiJi2 tt on hh.mocode=tt.生产订单号 group by 生产订单号
select 生产订单 ,AB面,产品名称,计划完工日期,Convert(decimal(18,0),计划数量) 计划数量, Convert(decimal(18,0),完成数量) 完成数量, case when 计划数量 is not null and 计划数量!=0 then cast(Convert(decimal(18,2),完成数量*1.0000/计划数量*100) as nvarchar) +'%' else '0%' end 生产进度 from ( select gg.生产订单号 生产订单,gg.AB面, gg.产品名称, gg.计划完工日期, gg.计划量 计划数量, (select sum(lotqty) from #TempCaiJi2 cc where cc.生产订单号=gg.生产订单号 and cc.生产订单行号=gg.生产订单行号 and cc.AB面=gg.AB面 ) 完成数量
from ( select cc.AB面,mm.生产订单号, mm.生产订单行号, mm.计划完工日期,mm.产品名称, mm.计划量 ,mm.线体 from #TempCaiJi2 cc left join #TempMOSelect2 mm with (nolock) on cc.lotno=mm.lotno group by cc.AB面,mm.生产订单号, mm.生产订单行号, mm.计划完工日期,mm.产品名称, mm.计划量,mm.线体 ) gg left join #Temp_MINDaJian2 cc2 on cc2.生产订单号=gg.生产订单号 ) jj order by 2,1,3 ";
} else { if (ItemNumber == "60") { where1 = " and (MO.itemcode like '60%' or MO.itemcode like '70%' ) "; } else { where1 = " and (MO.itemcode like '90%' ) "; }
SqlText = @"
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMOComQty')) drop table #TempMOComQty select --SUM( dd.lotqty) Qty ,sum(cc.qty) COMQTY,dd.transno mocode ,dd.transline INTO #TempMOComQty from icsitemlot dd with (nolock)left join ICSITEMLot2Carton cc with (nolock) on cc.lotno_ID=dd.IDinner join icsmo mo with (nolock) on mo.mocode=dd.transno and mo.moseq=dd.transline where mo.MOPLANENDDATE>=dateadd(mm,-1,getdate()) " + where1 + @"--and dd.lotno in (select ii.snno lotno from ICSAIODATA ii ) group by dd.transno ,dd.transline
select mo.mocode 生产订单,inv.invname 产品名称, Convert(decimal(18,1),mo.moplanqty) 计划数量,Convert(decimal(18,1),isnull(QQ.COMQTY,0)) 完成数量, CONVERT(varchar(10), mo.MOPLANENDDATE , 21) 计划完工日期, cast(Convert(decimal(18,2),isnull(QQ.COMQTY,0)*1.00/mo.moplanqty*100) as NVARCHAR)+'%' 生产进度 from icsmo mo with (nolock) left join #TempMOComQty QQ on mo.mocode=qq.mocode and mo.moseq=qq.transline left join ICSINVENTORY inv with (nolock) on inv.invcode=mo.itemcode where isnull(QQ.COMQTY,0)<mo.moplanqty and qq.mocode is not null
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMOComQty')) drop table #TempMOComQty ";
}
DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "MESconnstr", null);
return dt; }
public DataTable GetZhiXing_OQC( ) {
string SqlText = "";
SqlText = @"
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempIPPCC')) drop table #TempIPPCC if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMonthcc')) drop table #TempMonthcc declare @begin datetime,@end datetime set @end=GETDATE() declare @months int set @months=6 select convert(varchar(7),DATEADD(month,number*-1+1,@end) ,120) AS 月份 into #TempMonthcc from master.dbo.spt_values where type='p' AND number<=@months and number>=1 order by 1 select count(1) Qty,result,月份 into #TempIPPCC from ( select distinct ccode, invcode ,result,convert(varchar(7),mtime,120) 月份
from ICSOQCLotNo where mtime>=DATEADD(month,DATEDIFF(month,0,DATEADD(mm, -5, GETDATE() )),0) and result in('合格','不合格') ) dd group by 月份,result
select isnull( case when isnull(gg.QtyAll,0) =0 then 0 else Convert(decimal(18,2), (isnull(gg.QtyAll,0)-isnull(FF.QtyNG,0))*1.00/gg.QtyAll*100) end ,0) PercentNum, cc.月份 FROM #TempMonthcc cc left join (select sum(qty) QtyAll,月份 from #TempIPPCC hh group by 月份 ) GG on cc.月份=gg.月份 left join (select isnull(sum(qty),0) QtyNG,月份 from #TempIPPCC hh where result='不合格' group by 月份 ) ff on cc.月份=ff.月份 ORDER BY cc.月份 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempIPPCC')) drop table #TempIPPCC if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempMonthcc')) drop table #TempMonthcc ";
DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "MESconnstr", null);
return dt; }
public DataTable GetZhiXing_RCVUnInWare() {
string SqlText = "";
SqlText = @"
select A.CCODE 到货单号, B.LOTNO,a.cInvCode as 物料编码,C.INVSTD 规格型号,C.INVNAME 物料名称,b.transno 采购订单号, A.iQuantity AS 数量,A.STNO 送货单号 ,CONVERT(varchar(100), a.MTime, 23) MTime , STUFF((select',' + MUSERName from (SELECT DISTINCT MUSERName FROM ICSOQCLotNo d where D.CCODE=a.cCode and d.InvCode=a.cInvCode ) DD for xml path('')),1,1,'') 检验人 from ICSPOArrive a with(nolock) inner join icsitemlot b with(nolock) on a.LOTNO=b.LotNO LEFT JOIN ICSINVENTORY C with(nolock) ON C.INVCODE = A.cInvCode where EXISTS(select 1 from ICSOQCLotNo d with(nolock) where D.CCODE=a.cCode and d.InvCode=a.cInvCode and result='合格' ) and not EXISTS(select 1 from ICSWareHouseLotInfoLog ll with(nolock) where ll.lotno=a.lotno and ll.TransType='收') order by A.CCODE,a.cInvCode,B.LOTNO ";
DataTable dt = Repository().GetDataTableBySql_Other(SqlText, "MESconnstr", null);
return dt; } #endregion
//成品发货一览数据源
public DataTable Get_Table1() {
string SqlText = "";
SqlText = @"
select a.SDNCode as '发货单号' ,ArriveDate,cast(getdate()-a.ArriveDate as int) as '滞留天数',case when ISNULL(a.EATTRIBUTE3,'0')='0' then '未拣配' when ISNULL(a.EATTRIBUTE3,'0')='1' then '拣配中' when ISNULL(a.EATTRIBUTE3,'0')='2' then '拣配完成' end as '状态',a.CreatePerson as '管理员' FROM ICSSDN a left join [UFDATA_001_2021].dbo.DispatchLists dls on dls.DLID = a.SDNID and dls.irowno = a.Sequence WHERE a.WorkPoint='UFDATA_001_2021' AND a.Type='1' AND a.Status<>'3' and a.Quantity-a.SDNQuantity>0 and a.SDNQuantity + dls.fOutQuantity <> a.Quantity and dls.iQuantity>dls.fOutQuantity and a.WHCode not in ('018','019','020') ";
DataTable dt = SqlHelper.GetDataTableBySql(SqlText);
return dt; }
//采购未到货一览
public DataTable Get_Table2() {
string SqlText = "";
SqlText = @"
select a.DNCode as '到货单',a.InvCode as '存货名称',b.InvStd as '规格型号',a.Quantity as '应到数量', a.Quantity-a.RCVQuantity as '未入库数量' ,c.VenName as '供应商'from ICSDeliveryNotice a inner join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPointinner join dbo.ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPointwhere DNType='1' and a.Quantity-a.RCVQuantity>0";
DataTable dt = SqlHelper.GetDataTableBySql(SqlText);
return dt; }
//成品未入库
public DataTable Get_Table3() {
string SqlText = "";
SqlText = @"
select f.DepName as '产线',e.InvName as '存货名称',sum(case when e.AmountEnable='1' then c.Quantity/isnull(e.EATTRIBUTE1,1) else c.Quantity end ) as '数量' , CASE WHEN DATEDIFF(MINUTE, c.ProductDate, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, c.ProductDate, GETDATE()) AS VARCHAR) + '分钟' WHEN DATEDIFF(HOUR, c.ProductDate, GETDATE()) < 24 THEN CAST(DATEDIFF(HOUR, c.ProductDate, GETDATE()) AS VARCHAR) + '小时 ' + CAST((DATEDIFF(MINUTE, c.ProductDate, GETDATE()) % 60) AS VARCHAR) + '分钟' ELSE CAST((DATEDIFF(HOUR, c.ProductDate, GETDATE()) / 24) AS VARCHAR) + '天 ' + CAST((DATEDIFF(HOUR, c.ProductDate, GETDATE()) % 24) AS VARCHAR) + '小时 ' + CAST((DATEDIFF(MINUTE, c.ProductDate, GETDATE()) % 60) AS VARCHAR) + '分钟' END AS '滞留时间', CASE WHEN DATEDIFF(MINUTE, c.ProductDate, GETDATE()) >= 360 THEN 'red' WHEN DATEDIFF(MINUTE, c.ProductDate, GETDATE()) >= 180 THEN 'yellow' ELSE 'green' END AS Status from icsmo a inner join dbo.ICSInventoryLotDetail b on a.MOCode=b.TransCode and a.Sequence=b.TransSequence and a.WorkPoint=b.WorkPointinner join dbo.ICSInventoryLot c on b.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint and c.Type='3'inner join dbo.ICSInventory e on a.InvCode=e.InvCode and a.WorkPoint=e.WorkPointleft join dbo.ICSWareHouseLotInfo d on c.LotNo=d.LotNo and a.WorkPoint=b.WorkPointinner join ICSDepartment f on a.DepCode=f.DepCode and a.WorkPoint=f.WorkPointwhere isnull(d.LotNo,'')=''group by c.ProductDate,e.InvName,f.DepNameorder by f.DepName desc , c.ProductDate asc ";
DataTable dt = SqlHelper.GetDataTableBySql(SqlText);
return dt; }
//委外加工状态一览
public DataTable Get_Table4() {
string SqlText = "";
SqlText = @"
SELECT A.OOCode AS '委外订单号','' as '状态','' as '出库单号',MOS.dStartDate as '发单时间' FROM ICSOutsourcingOrder a --主表 left join [UFDATA_001_2021].dbo.OM_MOMain MO on MO.cCode = a.OOCode --子表 left join [UFDATA_001_2021].dbo.OM_MODetails MOS on MOS.MOID = MO.MOID AND a.Sequence=MOS.iVouchRowNo --子件表 left join [UFDATA_001_2021].dbo.OM_MOMaterials MOM on MOM.MoDetailsID = MOS.MoDetailsID where MOM.iQuantity>iSendQTY and MO.cState='1' GROUP BY A.OOCode,MOS.dStartDate order by MOS.dStartDate desc";
DataTable dt = SqlHelper.GetDataTableBySql(SqlText) ;
return dt; } //委外到货
public DataTable Get_Table5() {
string SqlText = "";
SqlText = @"
select a.ODNCode as '到货单',a.InvCode as '物料编码',b.InvStd as '规格型号',a.Quantity as '应到数量', a.Quantity-a.RCVQuantity as '未入库数量' ,c.VenName as '供应商'from ICSODeliveryNotice a inner join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPointinner join dbo.ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPointwhere ODNType='1' and a.Quantity-a.RCVQuantity>0";
DataTable dt = SqlHelper.GetDataTableBySql(SqlText);
return dt; } //调拨
public DataTable Get_Table6() {
string SqlText = "";
// SqlText = @"
// select a.存货编码,a.存货名称,a.数量,a.目标仓库,
// case when a.产线='021' then 'L1' when a.产线='022' then 'L2' when a.产线='023' then 'L3' when a.产线='024' then 'L4'
// when a.产线='025' then 'L5' when a.产线='026' then 'L6' when a.产线='027' then 'L7' when a.产线='015' then 'C1' end as '产线'
//,a.调拨时间 from (
// select a.存货编码,a.存货名称,
//sum(case when a.Flag='负' then -1*a.数量 else a.数量 end ) as 数量,
//case when a.仓库F not in ('021','022','023','024','025','026','027','015') and a.仓库T in ('021','022','023','024','025','026','027','015') then a.仓库T
//when a.仓库F = '015' and a.仓库T in ('021','022','023','024','025','026','027') then a.仓库T
//when a.仓库F in ('021','022','023','024','025','026','027') and a.仓库T='015' then a.仓库F
//else a.仓库F end as '产线'
// ,a.调拨时间 ,a.仓库T as '目标仓库'
// from(
// select a.InvCode as '存货编码',b.InvName+'|'+b.InvStd as '存货名称',
// case when b.AmountEnable='1' then a.Quantity/b.EATTRIBUTE1 else a.Quantity end '数量',
// a.FromWarehouseCode as '仓库F',
// a.ToWarehouseCode as '仓库T',
// case when a.FromWarehouseCode not in('021','022','023','024','025','026','027','015') and a.ToWarehouseCode in ('021','022','023','024','025','026','027','015') then '正' else '负' end Flag ,
// format(a.MTIME , 'yyyy-MM-dd') as '调拨时间'
// from dbo.ICSWareHouseLotInfoLog a
// inner join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
// inner join dbo.ICSWarehouse c on a.ToWarehouseCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint
// where BusinessCode in ('70','47') and a.TransType in ('6','14') and format(a.MTIME , 'yyyy-MM-dd') =format(getdate() , 'yyyy-MM-dd')
// and (a.FromWarehouseCode in('021','022','023','024','025','026','027')or a.ToWarehouseCode in('021','022','023','024','025','026','027'))
// )a
// group by a.调拨时间 ,a.存货编码,a.存货名称,a.仓库F,a.仓库T
// ) a ";
SqlText = @"select a.调拨仓,a.存货编码,a.存货名称,cast(sum(a.数量 )as decimal(16,2) ) 数量,a.目标仓,a.调拨时间
from( select a.InvCode as '存货编码',b.InvName+'|'+b.InvStd as '存货名称', case when b.AmountEnable='1' then a.Quantity/b.EATTRIBUTE1 else a.Quantity end '数量', case when a.FromWarehouseCode='021' then a.FromWarehouseCode+'/'+'L1' when a.FromWarehouseCode='022' then a.FromWarehouseCode+'/'+'L2' when a.FromWarehouseCode='023' then a.FromWarehouseCode+'/'+'L3' when a.FromWarehouseCode='024' then a.FromWarehouseCode+'/'+'L4' when a.FromWarehouseCode='025' then a.FromWarehouseCode+'/'+'L5' when a.FromWarehouseCode='026' then a.FromWarehouseCode+'/'+'L6' when a.FromWarehouseCode='027' then a.FromWarehouseCode+'/'+'L7' when a.FromWarehouseCode='015' then a.FromWarehouseCode+'/'+'C1' else a.FromWarehouseCode+'/'+c.WarehouseName end as '调拨仓',
case when a.ToWarehouseCode='021' then a.ToWarehouseCode+'/'+'L1' when a.ToWarehouseCode='022' then a.ToWarehouseCode+'/'+'L2' when a.ToWarehouseCode='023' then a.ToWarehouseCode+'/'+'L3' when a.ToWarehouseCode='024' then a.ToWarehouseCode+'/'+'L4' when a.ToWarehouseCode='025' then a.ToWarehouseCode+'/'+'L5' when a.ToWarehouseCode='026' then a.ToWarehouseCode+'/'+'L6' when a.ToWarehouseCode='027' then a.ToWarehouseCode+'/'+'L7' when a.ToWarehouseCode='015' then a.ToWarehouseCode+'/'+'C1' else a.ToWarehouseCode+'/'+d.WarehouseName end as '目标仓', format(a.MTIME , 'yyyy-MM-dd') as '调拨时间' from dbo.ICSWareHouseLotInfoLog a inner join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint inner join dbo.ICSWarehouse c on a.FromWarehouseCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint inner join dbo.ICSWarehouse d on a.ToWarehouseCode=d.WarehouseCode and a.WorkPoint=d.WorkPoint where BusinessCode in ('70','47') and a.TransType in ('6','14') and format(a.MTIME , 'yyyy-MM-dd') =format(getdate() , 'yyyy-MM-dd') )a group by a.调拨时间 ,a.存货编码,a.存货名称,a.调拨仓,a.目标仓";
DataTable dt = SqlHelper.GetDataTableBySql(SqlText);
return dt; }
//库存
public DataTable Get_KCInfo() {
string SqlText = "";
SqlText = @"
select cast(sum( case when a.WarehouseCode='001' then a.Quantity else 0 end )/1000 as decimal(18,2)) as YCL,cast(sum( case when a.WarehouseCode='003' then a.Quantity else 0 end )/1000 as decimal(18,2)) as CP,cast(sum( case when a.WarehouseCode='004' then a.Quantity else 0 end )/1000 as decimal(18,2))as FQ,cast(sum( case when a.WarehouseCode='005' then a.Quantity else 0 end )/1000 as decimal(18,2))as FCP,cast(sum( case when a.WarehouseCode='006' then a.Quantity else 0 end )/1000 as decimal(18,2))as ZLL,cast(sum(case when a.WarehouseCode='002' then a.Quantity/isnull(b.EATTRIBUTE1,1) else 0 end ) as decimal(18,2)) as BHM from dbo.ICSWareHouseLotInfo a left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint";
DataTable dt = SqlHelper.GetDataTableBySql(SqlText);
return dt; }
//数据条数
public DataTable Get_InfoNum() {
string SqlText = "";
SqlText = @"
select count(*) as CPFHinto #Temp1 from ( select a.SDNCode as '发货单号' ,ArriveDate,cast(getdate()-a.ArriveDate as int) as '滞留天数',case when ISNULL(a.EATTRIBUTE3,'0')='0' then '未拣配' when ISNULL(a.EATTRIBUTE3,'0')='1' then '拣配中' when ISNULL(a.EATTRIBUTE3,'0')='2' then '拣配完成' end as '状态',a.CreatePerson as '管理员' FROM ICSSDN a left join [UFDATA_001_2021].dbo.DispatchLists dls on dls.DLID = a.SDNID and dls.irowno = a.Sequence WHERE a.WorkPoint='UFDATA_001_2021' AND a.Type='1' AND a.Status<>'3' and a.Quantity-a.SDNQuantity>0 and a.SDNQuantity + dls.fOutQuantity <> a.Quantity and dls.iQuantity>dls.fOutQuantity and a.WHCode not in ('018','019','020'))a
select count(*) as CGWDH into #Temp2 from (select a.DNCode as '到货单',a.InvCode as '存货名称',b.InvStd as '规格型号',a.Quantity as '应到数量', a.Quantity-a.RCVQuantity as '未入库数量' ,c.VenName as '供应商'from ICSDeliveryNotice a inner join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPointinner join dbo.ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPointwhere DNType='1' and a.Quantity-a.RCVQuantity>0)b
select count(*) as CPWRK into #Temp3 from (select f.DepName as '产线',e.InvName as '存货编码',sum(case when e.AmountEnable='1' then c.Quantity/isnull(e.EATTRIBUTE1,1) else c.Quantity end ) as '数量' , CASE WHEN DATEDIFF(MINUTE, c.ProductDate, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, c.ProductDate, GETDATE()) AS VARCHAR) + '分钟' WHEN DATEDIFF(HOUR, c.ProductDate, GETDATE()) < 24 THEN CAST(DATEDIFF(HOUR, c.ProductDate, GETDATE()) AS VARCHAR) + '小时 ' + CAST((DATEDIFF(MINUTE, c.ProductDate, GETDATE()) % 60) AS VARCHAR) + '分钟' ELSE CAST((DATEDIFF(HOUR, c.ProductDate, GETDATE()) / 24) AS VARCHAR) + '天 ' + CAST((DATEDIFF(HOUR, c.ProductDate, GETDATE()) % 24) AS VARCHAR) + '小时 ' + CAST((DATEDIFF(MINUTE, c.ProductDate, GETDATE()) % 60) AS VARCHAR) + '分钟' END AS '滞留时间', CASE WHEN DATEDIFF(MINUTE, c.ProductDate, GETDATE()) >= 360 THEN 'red' WHEN DATEDIFF(MINUTE, c.ProductDate, GETDATE()) >= 180 THEN 'yellow' ELSE 'green' END AS Status from icsmo a inner join dbo.ICSInventoryLotDetail b on a.MOCode=b.TransCode and a.Sequence=b.TransSequence and a.WorkPoint=b.WorkPointinner join dbo.ICSInventoryLot c on b.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint and c.Type='3'inner join dbo.ICSInventory e on a.InvCode=e.InvCode and a.WorkPoint=e.WorkPointleft join dbo.ICSWareHouseLotInfo d on c.LotNo=d.LotNo and a.WorkPoint=b.WorkPointinner join ICSDepartment f on a.DepCode=f.DepCode and a.WorkPoint=f.WorkPointwhere isnull(d.LotNo,'')=''group by c.ProductDate,e.InvName,f.DepName)c
select count(*) as WWJG into #Temp4 from (SELECT A.OOCode AS '委外订单号','' as '状态','' as '出库单号',MOS.dStartDate as '发单时间' FROM ICSOutsourcingOrder a --主表 left join [UFDATA_001_2021].dbo.OM_MOMain MO on MO.cCode = a.OOCode --子表 left join [UFDATA_001_2021].dbo.OM_MODetails MOS on MOS.MOID = MO.MOID AND a.Sequence=MOS.iVouchRowNo --子件表 left join [UFDATA_001_2021].dbo.OM_MOMaterials MOM on MOM.MoDetailsID = MOS.MoDetailsID where MOM.iQuantity>iSendQTY and MO.cState='1' GROUP BY A.OOCode,MOS.dStartDate )d
select count(*) as DB into #Temp5 from(select a.调拨仓,a.存货编码,a.存货名称,cast(sum(a.数量 )as decimal(16,2) ) 数量,a.目标仓,a.调拨时间 from( select a.InvCode as '存货编码',b.InvName+'|'+b.InvStd as '存货名称', case when b.AmountEnable='1' then a.Quantity/b.EATTRIBUTE1 else a.Quantity end '数量', case when a.FromWarehouseCode='021' then a.FromWarehouseCode+'/'+'L1' when a.FromWarehouseCode='022' then a.FromWarehouseCode+'/'+'L2' when a.FromWarehouseCode='023' then a.FromWarehouseCode+'/'+'L3' when a.FromWarehouseCode='024' then a.FromWarehouseCode+'/'+'L4' when a.FromWarehouseCode='025' then a.FromWarehouseCode+'/'+'L5' when a.FromWarehouseCode='026' then a.FromWarehouseCode+'/'+'L6' when a.FromWarehouseCode='027' then a.FromWarehouseCode+'/'+'L7' else a.FromWarehouseCode+'/'+c.WarehouseName end as '调拨仓',
case when a.ToWarehouseCode='021' then a.ToWarehouseCode+'/'+'L1' when a.ToWarehouseCode='022' then a.ToWarehouseCode+'/'+'L2' when a.ToWarehouseCode='023' then a.ToWarehouseCode+'/'+'L3' when a.ToWarehouseCode='024' then a.ToWarehouseCode+'/'+'L4' when a.ToWarehouseCode='025' then a.ToWarehouseCode+'/'+'L5' when a.ToWarehouseCode='026' then a.ToWarehouseCode+'/'+'L6' when a.ToWarehouseCode='027' then a.ToWarehouseCode+'/'+'L7' else a.ToWarehouseCode+'/'+d.WarehouseName end as '目标仓', format(a.MTIME , 'yyyy-MM-dd') as '调拨时间' from dbo.ICSWareHouseLotInfoLog a inner join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint inner join dbo.ICSWarehouse c on a.FromWarehouseCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint inner join dbo.ICSWarehouse d on a.ToWarehouseCode=d.WarehouseCode and a.WorkPoint=d.WorkPoint where BusinessCode in ('70','47') and a.TransType in ('6','14') and format(a.MTIME , 'yyyy-MM-dd') =format(getdate() , 'yyyy-MM-dd') )a group by a.调拨时间 ,a.存货编码,a.存货名称,a.调拨仓,a.目标仓
)e
select count(*) as WWWDH into #Temp6 from (select (select count(*) from ICSODeliveryNotice a inner join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPointinner join dbo.ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPointwhere ODNType='1' and a.Quantity-a.RCVQuantity>0) as co, a.ODNCode as '到货单',a.InvCode as '物料编码',b.InvStd as '规格型号',a.Quantity as '应到数量', a.Quantity-a.RCVQuantity as '未入库数量' ,c.VenName as '供应商'from ICSODeliveryNotice a inner join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPointinner join dbo.ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPointwhere ODNType='1' and a.Quantity-a.RCVQuantity>0)f
select a.CPFH,b.CGWDH,c.CPWRK,d.WWJG,e.DB,f.WWWDH from #Temp1 a left join #Temp2 b on 1=1left join #Temp3 c on 1=1left join #Temp4 d on 1=1left join #Temp5 e on 1=1left join #Temp6 f on 1=1
drop table #Temp1,#Temp2,#Temp3,#Temp4,#Temp5,#Temp6";
DataTable dt = SqlHelper.GetDataTableBySql(SqlText);
return dt; }
}}
|
