/// <summary>
/// 套餐
/// </summary>
/// <returns></returns>
public static List <Report> GetPInfoAll(LoginEx iiLoginEx, string tableName)
{
iLoginEx = iiLoginEx;
List <Report> lst = new List <Report>();
try
{
OleDbConnection con = GetCon();
OpenCon();
string sql = "select * from " + tableName + "";
OleDbCommand com = new OleDbCommand(sql, con);
com.CommandTimeout = 3600;
OleDbDataReader dr = com.ExecuteReader();
while (dr.Read())
{
Report r = FieldInfo(dr);
lst.Add(r);
}
dr.Close();
CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("错误：" + ex.Message);
}
return(lst);
}
/// <summary>
/// 地区国内分组
/// </summary>
/// <param name="iiLoginEx"></param>
/// <returns></returns>
public static List <Report> GroupByRCCCDistrict(LoginEx iiLoginEx, string district)
{
iLoginEx = iiLoginEx;
List <Report> lst = new List <Report>();
try
{
OleDbConnection con = GetCon();
OpenCon();
string sql = "select * from zhrs_t_RegioncountryCitycustomer where District like '%" + district + "%'";
OleDbCommand com = new OleDbCommand(sql, con);
com.CommandTimeout = 3600;
OleDbDataReader dr = com.ExecuteReader();
while (dr.Read())
{
Report r = FieldInfo(dr);
lst.Add(r);
}
dr.Close();
CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("错误：" + ex.Message);
}
return(lst);
}
/// <summary>
/// 添加欠料表原采购回复
/// </summary>
/// <param name="iLoginEx"></param>
/// <param name="i"></param>
/// <returns></returns>
public static int AddMaterialShortage(LoginEx iiLoginEx, Infolst i)
{
iLoginEx = iiLoginEx;
int n = 0;
try
{
OleDbConnection con = GetCon();
OpenCon();
string sql = string.Format("insert into zhrs_t_zzcMaterialShortage(MCoding,MDate,MNumber,MExplain,MDescribe,MOther)values('{0}','{1}','{2}','{3}','{4}','{5}')", i.MCoding, i.Date, i.Num, i.Explain, i.Describe, i.MOther);
OleDbCommand com = new OleDbCommand(sql, con);
com.CommandTimeout = 3600;
n = com.ExecuteNonQuery();
CloseCon();
}
catch (Exception ex)
{
MessageBox.Show("错误： " + ex.Message);
}
finally
{
GC.Collect();
GC.Collect(1);
}
return(n);
}
/// <summary>
/// 筛选 地区+国家/城市
/// </summary>
/// <param name="iiloginEx"></param>
/// <param name="r"></param>
/// <returns></returns>
public static List <Report> GetFindRCCInfoAllConn(LoginEx iiloginEx, Report r, string tableName)
{
List <Report> rep = new List <Report>();
rep = GetRCCInfoAll(iiloginEx, tableName).FindAll((p) => p.District.IndexOf(r.District) >= 0);
return(rep);
}
/// <summary>
/// 筛选 地区+国家/城市+客户
/// </summary>
/// <param name="iiloginEx"></param>
/// <param name="r"></param>
/// <returns></returns>
public static List <Report> GetFindRCCCInfoAllConn(LoginEx iiloginEx, Report r, string tableName)
{
List <Report> rep = new List <Report>();
try
{
rep = GetRCCCInfoAll(iiloginEx, tableName).FindAll((p) => p.District.IndexOf(r.District) >= 0);
}
catch (Exception ex)
{
MessageBox.Show("异常：" + ex.Message);
}
return(rep);
}
/// <summary>
/// 模糊套餐
/// </summary>
/// <param name="iiLoginEx"></param>
/// <param name="r"></param>
/// <returns></returns>
public static List <Report> GetSelectPInfoAll(LoginEx iiLoginEx, Report r, string tableName)
{
List <Report> rep = new List <Report>();
try
{
rep = GetPInfoAll(iiLoginEx, tableName).FindAll((p) => p.cInvCCode.IndexOf(r.cInvCCode) >= 0
);
}
catch (Exception ex)
{
MessageBox.Show("异常：" + ex.Message);
}
return(rep);
}
/// <summary>
/// 模糊 客户
/// </summary>
/// <param name="iiLoginEx"></param>
/// <param name="r"></param>
/// <returns></returns>
public static List <Report> GetSelectCustomerInfoAll(LoginEx iiLoginEx, Report r, string tableName)
{
List <Report> rep = new List <Report>();
try
{
rep = GetCustomerInfoAll(iiLoginEx, "", tableName).FindAll((p) =>
p.ccusname.IndexOf(r.ccusname) >= 0 ||
p.cPersonName.IndexOf(r.cPersonName) >= 0);
}
catch (Exception ex)
{
MessageBox.Show("异常：" + ex.Message);
}
return(rep);
}
public StockQuery(LoginEx iiLoginEx, int iAuthID)
{
InitializeComponent();
iLoginEx = iiLoginEx;
AuthID = iAuthID;
}
/// <summary>
/// 库存综合查询
/// </summary>
/// <param name="iLoginEx"></param>
/// <param name="cType">0=库存查询，显示所有；1=请购单导入（不仅显示在制）；2=制造单导入（仅显示现存量、在制、已分配量）；3=仅显示替代料</param>
/// <param name="cInvCode">物料编码</param>
/// <param name="cDateL">日期L</param>
/// <param name="cDateH">日期H</param>
/// <param name="UFsystem">用友中间数据库名</param>
/// <param name="cWhCode">仓库代码</param>
/// <param name="iKeys">查询条件</param>
/// <param name="tempTableName">临时表名</param>
/// <param name="CurSotckQtySource">初始库存类型（MRP）：0=无；1=现存量；2=安全库存；3=临时库存</param>
/// <param name="TimeRange">true=考虑时间范围</param>
/// <returns></returns>
public string ComprehensiveStockInfo(LoginEx iLoginEx, ushort cType, string cInvCode, string cDateL, string cDateH, string UFsystem, string cWhCode, string iKeys, string tempTableName, ushort CurSotckQtySource, bool TimeRange)
{
//--1=请购单
//--2=采购订单
//--3=采购到货单
//--4=现存量
//--5=生产订单(在制)
//--6=销售订单
//--7=生产订单（需求）
//--8=替代料
//--9=委外订单（在制）
//--10=委外订单（需求）
//--11=新需求（MRP）
//销售订单
string SoDateL = iLoginEx.ReadUserProfileValue("MRP_Parameter", "SoDateL").Length == 0 ? "2000-01-01" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "SoDateL");
string SoDateH = iLoginEx.ReadUserProfileValue("MRP_Parameter", "SoDateH").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "SoDateH");
//生产订单
string MoStartDateL = iLoginEx.ReadUserProfileValue("MRP_Parameter", "MoStartDateL").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "MoStartDateL");
string MoStartDateH = iLoginEx.ReadUserProfileValue("MRP_Parameter", "MoStartDateH").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "MoStartDateH");
//委外订单
string OsDateL = iLoginEx.ReadUserProfileValue("MRP_Parameter", "OsDateL").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "OsDateL");
string OsDateH = iLoginEx.ReadUserProfileValue("MRP_Parameter", "OsDateH").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "OsDateH");
//采购订单
string PoDateL = iLoginEx.ReadUserProfileValue("MRP_Parameter", "PoDateL").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "PoDateL");
string PoDateH = iLoginEx.ReadUserProfileValue("MRP_Parameter", "PoDateH").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "PoDateH");
//采购到货单
string PurArrDateL = iLoginEx.ReadUserProfileValue("MRP_Parameter", "PurArrDateL").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "PurArrDateL");
string PurArrDateH = iLoginEx.ReadUserProfileValue("MRP_Parameter", "PurArrDateH").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "PurArrDateH");
//请购单
string PoApDateL = iLoginEx.ReadUserProfileValue("MRP_Parameter", "PoApDateL").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "PoApDateL");
string PoApDateH = iLoginEx.ReadUserProfileValue("MRP_Parameter", "PoApDateH").Length == 0 ? "2099-12-31" : iLoginEx.ReadUserProfileValue("MRP_Parameter", "PoApDateH");
OleDbConnection myConn = new OleDbConnection(iLoginEx.ConnString());
if (myConn.State == System.Data.ConnectionState.Open)
{
myConn.Close();
}
myConn.Open();
//用临时表，并分两段，以防止接连接超时
OleDbCommand myCommand = new OleDbCommand("", myConn);
myCommand.CommandTimeout = 7200;
//string ComprehensiveStockST31 = "tempdb..ST31" + iLoginEx.GetMacAddress().Replace(":", "") + iLoginEx.AccID() + DateTime.Now.ToString("yy-MM-dd HH:mm:ss:ffff").Replace("-", "").Replace(" ", "").Replace(":", "");
string ComprehensiveStockST31 = "";
if (tempTableName.Length <= 0)
{
tempTableName = "zhrserpST31" + iLoginEx.GetMacAddress().Replace(":", "") + iLoginEx.AccID();
}
ComprehensiveStockST31 = "tempdb.." + tempTableName;
string selectSQL = "";
selectSQL = " if exists (select 1 from tempdb.dbo.sysobjects (nolock) where upper(name)= upper('" + tempTableName + "') and type='U') \r\n";
selectSQL += " drop table " + ComprehensiveStockST31 + " ; \r\n";
selectSQL += " \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
selectSQL = "create table " + ComprehensiveStockST31 + " ( \r\n";
selectSQL += " Prod_cInvCode nvarchar(20) null default '', \r\n";
selectSQL += " DocTypeNo tinyint not null default 0, \r\n";
selectSQL += " DocType nvarchar(300) null default '', \r\n";
selectSQL += " cCode nvarchar(120) null default '', \r\n";
selectSQL += " cpersonname nvarchar(40) null default '', \r\n";
selectSQL += " cDefine30 nvarchar(120) null default '', \r\n";
selectSQL += " dDate datetime null, \r\n";
selectSQL += " cinvcode nvarchar(20) not null default '', \r\n";
selectSQL += " moQty decimal(28,4) null default 0, \r\n";
selectSQL += " Now_PurArrQty decimal(28,4) null default 0, \r\n";
selectSQL += " Now_PurQty decimal(28,4) null default 0, \r\n";
selectSQL += " CurSotckQty decimal(28,4) null default 0, \r\n";
selectSQL += " useQty decimal(28,4) null default 0, \r\n";
selectSQL += " toArrQty decimal(28,4) null default 0, \r\n";
selectSQL += " AltmQty decimal(28,4) null default 0, \r\n";
selectSQL += " OsQty decimal(28,4) null default 0) \r\n";
selectSQL += " \r\n";
selectSQL += " \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
if (cType == 0 || cType == 1)
{
selectSQL = " ----*到货量******** \r\n";
selectSQL += " \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " SELECT '' as 'Prod_cInvCode',DocTypeNo=3,DocType='采购到货单',Pu_ArrivalVouch.cCode,Pu_ArrivalVouch.cMaker as 'cpersonname',Pu_ArrivalVouchs.cDefine30,Pu_ArrivalVouch.dDate,Pu_ArrivalVouchs.cinvcode,moQty=0, \r\n";
selectSQL += " case when Pu_ArrivalVouchs.iquantity>0 then \r\n";
selectSQL += " case when \r\n";
selectSQL += " (case when isnull(Pu_ArrivalVouchs.bgsp,0)=0 then CONVERT(DECIMAL(28,6),(Pu_ArrivalVouchs.iquantity-ISNULL(fRefuseQuantity,0)-ISNULL(fInValidInQuan,0)-ISNULL(fValidInQuan,0))) \r\n";
selectSQL += " else isnull(Pu_ArrivalVouchs.iquantity,0) -ISNULL(fRefuseQuantity,0)- isnull(QmArrCheckSrv.FMinQty,0) - isnull(Pu_ArrivalVouchs.fdegradeinquantity,0) \r\n";
selectSQL += " - isnull(Pu_ArrivalVouchs.finvalidinquan,0) - isnull(Pu_ArrivalVouchs.fdtquantity,0) end) >=0 then \r\n";
selectSQL += " (case when isnull(Pu_ArrivalVouchs.bgsp,0)=0 then CONVERT(DECIMAL(28,6),(Pu_ArrivalVouchs.iquantity-ISNULL(fRefuseQuantity,0)-ISNULL(fInValidInQuan,0)-ISNULL(fValidInQuan,0))) \r\n";
selectSQL += " else isnull(Pu_ArrivalVouchs.iquantity,0) -ISNULL(fRefuseQuantity,0)- isnull(QmArrCheckSrv.FMinQty,0) - isnull(Pu_ArrivalVouchs.fdegradeinquantity,0) \r\n";
selectSQL += " - isnull(Pu_ArrivalVouchs.finvalidinquan,0) - isnull(Pu_ArrivalVouchs.fdtquantity,0) end) \r\n";
selectSQL += " else \r\n";
selectSQL += " 0 end \r\n";
selectSQL += " else \r\n";
selectSQL += " case when (case when isnull(Pu_ArrivalVouchs.bgsp,0)=0 then CONVERT(DECIMAL(28,6),(Pu_ArrivalVouchs.iquantity-ISNULL(fRefuseQuantity,0)-ISNULL(fInValidInQuan,0)-ISNULL(fValidInQuan,0))) \r\n";
selectSQL += " else isnull(Pu_ArrivalVouchs.iquantity,0) -ISNULL(fRefuseQuantity,0)- isnull(QmArrCheckSrv.FMinQty,0) - isnull(Pu_ArrivalVouchs.fdegradeinquantity,0) \r\n";
selectSQL += " - isnull(Pu_ArrivalVouchs.finvalidinquan,0) - isnull(Pu_ArrivalVouchs.fdtquantity,0) end) <=0 then \r\n";
selectSQL += " (case when isnull(Pu_ArrivalVouchs.bgsp,0)=0 then CONVERT(DECIMAL(28,6),(Pu_ArrivalVouchs.iquantity-ISNULL(fRefuseQuantity,0)-ISNULL(fInValidInQuan,0)-ISNULL(fValidInQuan,0))) \r\n";
selectSQL += " else isnull(Pu_ArrivalVouchs.iquantity,0) -ISNULL(fRefuseQuantity,0)- isnull(QmArrCheckSrv.FMinQty,0) - isnull(Pu_ArrivalVouchs.fdegradeinquantity,0) \r\n";
selectSQL += " - isnull(Pu_ArrivalVouchs.finvalidinquan,0) - isnull(Pu_ArrivalVouchs.fdtquantity,0) end) \r\n";
selectSQL += " else \r\n";
selectSQL += " 0 \r\n";
selectSQL += " end \r\n";
selectSQL += " end \r\n";
selectSQL += " AS Now_PurArrQty ,Now_PurQty=0,CurSotckQty=0 , useQty=0, toArrQty=0,AltmQty=0,OsQty=0 \r\n";
selectSQL += " FROM Pu_ArrivalVouchs (nolock) LEFT JOIN Pu_ArrivalVouch (nolock) ON Pu_ArrivalVouchs.ID=Pu_ArrivalVouch.ID \r\n";
selectSQL += " LEFT JOIN Inventory (nolock) ON Pu_ArrivalVouchs.cInvCode=Inventory.cInvCode \r\n";
selectSQL += " Left Join QmArrCheckSrv (nolock) on QmArrCheckSrv.SOURCEAUTOID=Pu_ArrivalVouchs.autoid \r\n";
selectSQL += " LEFT JOIN ComputationUnit as Unit1 (nolock) on Inventory.cPUComUnitCode=Unit1.cComUnitCode \r\n";
selectSQL += " LEFT JOIN So_Sodetails (nolock) ON PU_ArrivalVouchs.sodid=cast(So_Sodetails.isosid as nvarchar(60)) and PU_ArrivalVouchs.sotype =1 \r\n";
selectSQL += " LEFT JOIN v_mps_forecast (nolock) ON PU_ArrivalVouchs.sodid=cast(v_mps_forecast.ForecastDId as nvarchar(60)) and PU_ArrivalVouchs.sotype =2 \r\n";
selectSQL += " LEFT JOIN v_ex_order_forPUReport AS v_expo (nolock) ON PU_ArrivalVouchs.sodid=cast(v_expo.autoid as nvarchar(60)) and PU_ArrivalVouchs.sotype =3 \r\n";
selectSQL += " left join AA_RequirementClass (nolock) on AA_RequirementClass.cRClassCode=PU_ArrivalVouchs.sodid and PU_ArrivalVouchs.sotype =4 \r\n";
selectSQL += " left join so_somain (nolock) on so_somain.csocode=PU_ArrivalVouchs.sodid and PU_ArrivalVouchs.sotype =5 \r\n";
selectSQL += " left join ex_order (nolock) on ex_order.ccode=PU_ArrivalVouchs.sodid and PU_ArrivalVouchs.sotype =6 \r\n";
selectSQL += " WHERE len(isnull(PU_ArrivalVouch.cverifier,''))>0 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and Pu_ArrivalVouchs.cInvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where Pu_ArrivalVouchs.cInvCode=keys.cInvCode) \r\n";
}
}
if (TimeRange)
{
selectSQL += " and Pu_ArrivalVouch.dDate>='" + PurArrDateL + "' and Pu_ArrivalVouch.dDate<='" + PurArrDateH + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and Pu_ArrivalVouch.dDate>='" + cDateL + "' ";
}
if (cDateH.Length > 0)
{
selectSQL += " and Pu_ArrivalVouch.dDate<='" + cDateH + "' ";
}
}
selectSQL += " and len(isnull(PU_ArrivalVouch.ccloser,'')+isnull(PU_ArrivalVouchs.cbcloser,''))=0 and (len(isnull(Pu_ArrivalVouchs.cwhcode,''))=0 OR (inventory.bInvBatch=1 and len(ISNULL(Pu_ArrivalVouchs.cbatch,''))=0)) \r\n";
selectSQL += " AND ( (isnull(Pu_ArrivalVouch.bnegative,0)<>1 AND len(isnull(Pu_ArrivalVouchs.cbcloser,'')) = 0 ) or \r\n";
selectSQL += " (isnull(Pu_ArrivalVouchs.bgsp,0)=0 AND isnull(Pu_ArrivalVouch.bnegative,0)=1 AND len(isnull(Pu_ArrivalVouchs.cbcloser,'')) =0 and Pu_ArrivalVouch.iBillType = 1)) \r\n";
selectSQL += " and \r\n";
selectSQL += " ( \r\n";
selectSQL += " case when Pu_ArrivalVouchs.iquantity>0 then \r\n";
selectSQL += " case when \r\n";
selectSQL += " (case when isnull(Pu_ArrivalVouchs.bgsp,0)=0 then CONVERT(DECIMAL(28,6),(Pu_ArrivalVouchs.iquantity-ISNULL(fRefuseQuantity,0)-ISNULL(fInValidInQuan,0)-ISNULL(fValidInQuan,0))) \r\n";
selectSQL += " else isnull(Pu_ArrivalVouchs.iquantity,0) -ISNULL(fRefuseQuantity,0)- isnull(QmArrCheckSrv.FMinQty,0) - isnull(Pu_ArrivalVouchs.fdegradeinquantity,0) \r\n";
selectSQL += " - isnull(Pu_ArrivalVouchs.finvalidinquan,0) - isnull(Pu_ArrivalVouchs.fdtquantity,0) end) >=0 then \r\n";
selectSQL += " (case when isnull(Pu_ArrivalVouchs.bgsp,0)=0 then CONVERT(DECIMAL(28,6),(Pu_ArrivalVouchs.iquantity-ISNULL(fRefuseQuantity,0)-ISNULL(fInValidInQuan,0)-ISNULL(fValidInQuan,0))) \r\n";
selectSQL += " else isnull(Pu_ArrivalVouchs.iquantity,0) -ISNULL(fRefuseQuantity,0)- isnull(QmArrCheckSrv.FMinQty,0) - isnull(Pu_ArrivalVouchs.fdegradeinquantity,0) - isnull(Pu_ArrivalVouchs.finvalidinquan,0) \r\n";
selectSQL += " - isnull(Pu_ArrivalVouchs.fdtquantity,0) end) \r\n";
selectSQL += " else \r\n";
selectSQL += " 0 end \r\n";
selectSQL += " else \r\n";
selectSQL += " case when (case when isnull(Pu_ArrivalVouchs.bgsp,0)=0 then CONVERT(DECIMAL(28,6),(Pu_ArrivalVouchs.iquantity-ISNULL(fRefuseQuantity,0)-ISNULL(fInValidInQuan,0)-ISNULL(fValidInQuan,0))) \r\n";
selectSQL += " else isnull(Pu_ArrivalVouchs.iquantity,0) -ISNULL(fRefuseQuantity,0)- isnull(QmArrCheckSrv.FMinQty,0) - isnull(Pu_ArrivalVouchs.fdegradeinquantity,0) - isnull(Pu_ArrivalVouchs.finvalidinquan,0) \r\n";
selectSQL += " - isnull(Pu_ArrivalVouchs.fdtquantity,0) end) <=0 then \r\n";
selectSQL += " (case when isnull(Pu_ArrivalVouchs.bgsp,0)=0 then CONVERT(DECIMAL(28,6),(Pu_ArrivalVouchs.iquantity-ISNULL(fRefuseQuantity,0)-ISNULL(fInValidInQuan,0)-ISNULL(fValidInQuan,0))) \r\n";
selectSQL += " else isnull(Pu_ArrivalVouchs.iquantity,0) -ISNULL(fRefuseQuantity,0)- isnull(QmArrCheckSrv.FMinQty,0) - isnull(Pu_ArrivalVouchs.fdegradeinquantity,0) \r\n";
selectSQL += " - isnull(Pu_ArrivalVouchs.finvalidinquan,0) - isnull(Pu_ArrivalVouchs.fdtquantity,0) end) \r\n";
selectSQL += " else \r\n";
selectSQL += " 0 \r\n";
selectSQL += " end \r\n";
selectSQL += " end)<>0 \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
selectSQL = " \r\n";
selectSQL += " ----*请购单******** \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " SELECT '' as 'Prod_cInvCode',DocTypeNo=1,DocType='请购单',Pu_AppVouch.cCode,Pu_AppVouch.cMaker as 'cpersonname',Pu_AppVouchs.cDefine30,Pu_AppVouch.dDate,Pu_AppVouchs.cInvCode,moQty=0,Now_PurArrQty=0,fQuantity-ISNULL(iReceivedQTY,0) AS Now_PurQty,CurSotckQty=0, useQty=0, toArrQty=0,AltmQty=0,OsQty=0 \r\n";
selectSQL += " FROM Pu_AppVouch (nolock) INNER JOIN Pu_AppVouchs (nolock) ON Pu_AppVouch.ID=Pu_AppVouchs.ID \r\n";
selectSQL += " left outer join PurchaseType t (nolock) on t.cPTCode = Pu_AppVouch.cPTCode \r\n";
selectSQL += " LEFT outer JOIN So_Sodetails (nolock) ON Pu_AppVouchs.sodid=cast(So_Sodetails.isosid as nvarchar(60)) and Pu_AppVouchs.sotype =1 \r\n";
selectSQL += " --LEFT outer JOIN v_mps_forecast (nolock) ON Pu_AppVouchs.sodid=cast(v_mps_forecast.ForecastDId as nvarchar(60)) and Pu_AppVouchs.sotype =2 \r\n";
selectSQL += " LEFT outer JOIN v_ex_order_forPUReport AS v_expo (nolock) ON Pu_AppVouchs.sodid=cast(v_expo.autoid as nvarchar(60)) and Pu_AppVouchs.sotype =3 \r\n";
selectSQL += " LEFT outer join AA_RequirementClass (nolock) on AA_RequirementClass.cRClassCode=Pu_AppVouchs.sodid and Pu_AppVouchs.sotype =4 \r\n";
selectSQL += " LEFT outer join so_somain (nolock) on so_somain.csocode=Pu_AppVouchs.sodid and Pu_AppVouchs.sotype =5 \r\n";
selectSQL += " LEFT outer join ex_order (nolock) on ex_order.ccode=Pu_AppVouchs.sodid and Pu_AppVouchs.sotype =6 \r\n";
selectSQL += " WHERE 1=1 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and Pu_AppVouchs.cInvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where Pu_AppVouchs.cInvCode=keys.cInvCode) \r\n";
}
}
if (TimeRange)
{
selectSQL += " and Pu_AppVouch.dDate>='" + PoApDateL + "' and Pu_AppVouch.dDate<='" + PoApDateH + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and Pu_AppVouch.dDate>='" + cDateL + "' ";
}
if (cDateH.Length > 0)
{
selectSQL += " and Pu_AppVouch.dDate<='" + cDateH + "' ";
}
}
selectSQL += " and len(isnull(PU_AppVouchs.cbcloser,'')+isnull(PU_AppVouch.cCloser,''))=0 and Pu_AppVouch.cBusType<>'直运采购' and Pu_AppVouch.cBusType<>'固定资产' and Pu_AppVouch.cBusType<>'委外加工' \r\n";
selectSQL += " and (case when len(ISNULL(Pu_AppVouchs.cbCloser,''))>0 or isnull(t.bPTMPS_MRP,1) = 0 then 4 when len(ISNULL(Pu_AppVouch.cVerifier,''))>0 then 3 when len(ISNULL(Pu_AppVouch.cLocker,''))>0 then 2 else 1 end)<>4 \r\n";
selectSQL += " and fQuantity-ISNULL(iReceivedQTY,0)<>0 \r\n";
selectSQL += " and len(ISNULL(Pu_AppVouch.cVerifier,''))>0 \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
selectSQL = " \r\n";
selectSQL += " ----**采购单**** \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " SELECT '' as 'Prod_cInvCode',DocTypeNo=2,DocType='采购订单',Po_Pomain.cPoId as 'cCode',person.cpersonname,Po_Podetails.cDefine30,Po_Pomain.dPODate as 'dDate',Po_Podetails.cInvCode,moQty=0,Now_PurArrQty=0,(ISNULL(Po_Podetails.iQuantity,0)-ISNULL(iReceivedQty,0)-ISNULL(iArrQty,0)) as Now_PurQty, \r\n";
selectSQL += " CurSotckQty=0 , useQty=0, toArrQty=0,AltmQty=0,OsQty=0 \r\n";
selectSQL += " FROM Po_Pomain (nolock) INNER JOIN Po_Podetails (nolock) ON Po_Pomain.POID=Po_Podetails.POID \r\n";
selectSQL += " left outer join PurchaseType t (nolock) on t.cPTCode = Po_Pomain.cPTCode \r\n";
selectSQL += " LEFT outer JOIN So_Sodetails (nolock) ON Po_Podetails.sodid=cast(So_Sodetails.isosid as nvarchar(60)) and Po_Podetails.sotype =1 \r\n";
selectSQL += " LEFT outer JOIN v_ex_order_forPUReport AS v_expo (nolock) ON Po_Podetails.sodid=cast(v_expo.autoid as nvarchar(60)) and Po_Podetails.sotype =3 \r\n";
selectSQL += " LEFT outer join AA_RequirementClass (nolock) on AA_RequirementClass.cRClassCode=Po_Podetails.sodid and Po_Podetails.sotype =4 \r\n";
selectSQL += " LEFT outer join so_somain (nolock) on so_somain.csocode=Po_Podetails.sodid and Po_Podetails.sotype =5 \r\n";
selectSQL += " LEFT outer join ex_order (nolock) on ex_order.ccode=Po_Podetails.sodid and Po_Podetails.sotype =6 \r\n";
selectSQL += " left join person (nolock) on Po_Pomain.cpersoncode = person.cpersoncode \r\n";
selectSQL += " WHERE 1=1 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and Po_Podetails.cInvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where Po_Podetails.cInvCode=keys.cInvCode) \r\n";
}
}
if (TimeRange)
{
selectSQL += " and Po_Pomain.dPODate>='" + PoDateL + "' and Po_Pomain.dPODate<='" + PoDateH + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and Po_Pomain.dPODate>='" + cDateL + "' ";
}
if (cDateH.Length > 0)
{
selectSQL += " and Po_Pomain.dPODate<='" + cDateH + "' ";
}
}
selectSQL += " and len(isnull(PO_Podetails.cbCloser,'')+isnull(PO_Pomain.cCloser,''))=0 and Po_Pomain.cBusType<>'直运采购' and Po_Pomain.cBusType<>'固定资产' \r\n";
selectSQL += " and (case when len(ISNULL(po_podetails.cbcloser,''))>0 or isnull(t.bPTMPS_MRP,1) = 0 then 4 when ((len(isnull(Po_Pomain.cVerifier,N''))>0 and len(isnull(Po_Pomain.cChanger,N''))=0)or len(isnull(Po_Pomain.cChangVerifier,N''))>0 ) then 3 \r\n";
selectSQL += " when len(ISNULL(Po_Pomain.cLocker,''))>0 then 2 else 1 end)<>4 \r\n";
selectSQL += " and (ISNULL(Po_Podetails.iQuantity,0)-ISNULL(iReceivedQty,0)-ISNULL(iArrQty,0))<>0 \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
}
switch (CurSotckQtySource)
{
case 0: //无库存
{
break;
}
case 1: //现存量
{
selectSQL = " \r\n";
selectSQL += " ----*现存量******** \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " select '' as 'Prod_cInvCode',DocTypeNo=4,DocType='现存量','['+c.cWhCode+']'+c.cWhName as 'cCode','' as cpersonname,'' as 'cDefine30',null as 'dDate', k.cInvCode, moQty=0,Now_PurArrQty=0,Now_PurQty=0, CurSotckQty=sum(isnull(k.iQuantity,0) - isnull(k.fStopQuantity,0)), useQty=0, toArrQty=0 ,AltmQty=0,OsQty=0 \r\n";
selectSQL += " from CurrentStock k (nolock) left join Warehouse c (nolock) on k.cWhCode = c.cWhCode where 1=1 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and k.cInvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where k.cInvCode=keys.cInvCode) \r\n";
}
}
cWhCode = cWhCode.Replace("\r", "");
cWhCode = cWhCode.Replace("\n", "");
cWhCode = cWhCode.Replace("；", ";");
if (cWhCode.Length > 0)
{
string cWhCodeChild = "";
string[] paracWhCode = cWhCode.Split(';');
if (paracWhCode.Length > 0)
{
for (int i = 0; i < paracWhCode.Length; i++)
{
cWhCodeChild += "'" + paracWhCode[i].ToString() + "',";
}
selectSQL += " and k.cWhCode in (" + cWhCodeChild + "'\r\n'" + ") \r\n";
}
else
{
selectSQL += " and k.cWhCode ='" + cWhCode + "' \r\n";
}
}
selectSQL += " and c.bMRP=1 and (isnull(k.iQuantity,0) - isnull(k.fStopQuantity,0))<>0 \r\n";
selectSQL += " group by k.cInvCode,'['+c.cWhCode+']'+c.cWhName \r\n";
selectSQL += " \r\n";
selectSQL += " \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
if (cInvCode.Length > 0)
{
//替代料
//selectSQL += " UNION ALL \r\n";
selectSQL = " ---替代料 \r\n";
selectSQL += " \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " select '' as 'Prod_cInvCode',DocTypeNo=8,DocType='替代料','['+c.cWhCode+']'+c.cWhName as 'cCode','' as cpersonname,'' as 'cDefine30',null as 'dDate', \r\n";
if (cType == 3)
{
selectSQL += " k.cInvCode, \r\n";
}
else
{
selectSQL += " cInvCode='" + cInvCode + "', \r\n";
}
selectSQL += " moQty=0,Now_PurArrQty=0,Now_PurQty=0, CurSotckQty=0, useQty=0, toArrQty=0 ,AltmQty=sum(isnull(k.iQuantity,0) - isnull(k.fStopQuantity,0)),OsQty=0 \r\n";
selectSQL += " from CurrentStock k (nolock) left join Warehouse c (nolock) on k.cWhCode = c.cWhCode where 1=1 \r\n";
cWhCode = cWhCode.Replace("\r", "");
cWhCode = cWhCode.Replace("\n", "");
cWhCode = cWhCode.Replace("；", ";");
if (cWhCode.Length > 0)
{
string cWhCodeChild = "";
string[] paracWhCode = cWhCode.Split(';');
if (paracWhCode.Length > 0)
{
for (int i = 0; i < paracWhCode.Length; i++)
{
cWhCodeChild += "'" + paracWhCode[i].ToString() + "',";
}
selectSQL += " and k.cWhCode in (" + cWhCodeChild + "'\r\n'" + ") \r\n";
}
else
{
selectSQL += " and k.cWhCode ='" + cWhCode + "' \r\n";
}
}
selectSQL += " and c.bMRP=1 and (isnull(k.iQuantity,0) - isnull(k.fStopQuantity,0))<>0 \r\n";
selectSQL += " and exists (select 1 from ( \r\n";
selectSQL += " select cInvCodeAtnm as 'cInvCode' from " + iLoginEx.pubDB_UT() + "..AlternativeMateriel (nolock) where cAccID='" + iLoginEx.AccID() + "' and AtnmType=0 and Disabled=0 and isMRP=1 and cInvCode='" + cInvCode + "' \r\n";
if (TimeRange)
{
selectSQL += " and StartDate>='" + SoDateL + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and StartDate>='" + cDateL + "' \r\n";
}
}
selectSQL += " union all \r\n";
selectSQL += " select cInvCode from " + iLoginEx.pubDB_UT() + "..AlternativeMateriel (nolock) where cAccID='" + iLoginEx.AccID() + "' and AtnmType=0 and Disabled=0 and isMRP=1 and isTwoWay=1 and cInvCodeAtnm='" + cInvCode + "' \r\n";
if (TimeRange)
{
selectSQL += " and StartDate>='" + SoDateL + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and StartDate>='" + cDateL + "' \r\n";
}
}
selectSQL += " )altm where k.cInvCode=altm.cInvCode \r\n";
selectSQL += " ) \r\n";
selectSQL += " group by k.cInvCode,'['+c.cWhCode+']'+c.cWhName \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
}
break;
}
case 2: //安全库存
{
break;
}
case 3: //临时库存
{
selectSQL = " \r\n";
selectSQL += " ----*临时库存******** \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " select '' as 'Prod_cInvCode',DocTypeNo=4,DocType='临时库存','['+c.cWhCode+']'+c.cWhName as 'cCode','' as cpersonname,'' as 'cDefine30',null as 'dDate', k.cInvCode, moQty=0,Now_PurArrQty=0,Now_PurQty=0, CurSotckQty=sum(isnull(k.Qty,0)), useQty=0, toArrQty=0 ,AltmQty=0,OsQty=0 \r\n";
selectSQL += " from zhrs_t_MRP_CurrentStock k (nolock) left join Warehouse c (nolock) on k.cWhCode = c.cWhCode where 1=1 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and k.cInvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where k.cInvCode=keys.cInvCode) \r\n";
}
}
cWhCode = cWhCode.Replace("\r", "");
cWhCode = cWhCode.Replace("\n", "");
cWhCode = cWhCode.Replace("；", ";");
if (cWhCode.Length > 0)
{
string cWhCodeChild = "";
string[] paracWhCode = cWhCode.Split(';');
if (paracWhCode.Length > 0)
{
for (int i = 0; i < paracWhCode.Length; i++)
{
cWhCodeChild += "'" + paracWhCode[i].ToString() + "',";
}
selectSQL += " and k.cWhCode in (" + cWhCodeChild + "'\r\n'" + ") \r\n";
}
else
{
selectSQL += " and k.cWhCode ='" + cWhCode + "' \r\n";
}
}
selectSQL += " and c.bMRP=1 and isnull(k.Qty,0)<>0 \r\n";
selectSQL += " group by k.cInvCode,'['+c.cWhCode+']'+c.cWhName \r\n";
selectSQL += " \r\n";
selectSQL += " \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
if (cInvCode.Length > 0)
{
//替代料
//selectSQL += " UNION ALL \r\n";
selectSQL = " ---临时库存替代料 \r\n";
selectSQL += " \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " select '' as 'Prod_cInvCode',DocTypeNo=8,DocType='替代料','['+c.cWhCode+']'+c.cWhName as 'cCode','' as cpersonname,'' as 'cDefine30',null as 'dDate', \r\n";
if (cType == 3)
{
selectSQL += " k.cInvCode, \r\n";
}
else
{
selectSQL += " cInvCode='" + cInvCode + "', \r\n";
}
selectSQL += " moQty=0,Now_PurArrQty=0,Now_PurQty=0, CurSotckQty=0, useQty=0, toArrQty=0 ,AltmQty=sum(isnull(k.Qty,0)),OsQty=0 \r\n";
selectSQL += " from zhrs_t_MRP_CurrentStock k (nolock) left join Warehouse c (nolock) on k.cWhCode = c.cWhCode where 1=1 \r\n";
cWhCode = cWhCode.Replace("\r", "");
cWhCode = cWhCode.Replace("\n", "");
cWhCode = cWhCode.Replace("；", ";");
if (cWhCode.Length > 0)
{
string cWhCodeChild = "";
string[] paracWhCode = cWhCode.Split(';');
if (paracWhCode.Length > 0)
{
for (int i = 0; i < paracWhCode.Length; i++)
{
cWhCodeChild += "'" + paracWhCode[i].ToString() + "',";
}
selectSQL += " and k.cWhCode in (" + cWhCodeChild + "'\r\n'" + ") \r\n";
}
else
{
selectSQL += " and k.cWhCode ='" + cWhCode + "' \r\n";
}
}
selectSQL += " and c.bMRP=1 and isnull(k.Qty,0) <>0 \r\n";
selectSQL += " and exists (select 1 from ( \r\n";
selectSQL += " select cInvCodeAtnm as 'cInvCode' from " + iLoginEx.pubDB_UT() + "..AlternativeMateriel (nolock) where cAccID='" + iLoginEx.AccID() + "' and AtnmType=0 and Disabled=0 and isMRP=1 and cInvCode='" + cInvCode + "' \r\n";
if (TimeRange)
{
selectSQL += " and StartDate>='" + SoDateL + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and StartDate>='" + cDateL + "' \r\n";
}
}
selectSQL += " union all \r\n";
selectSQL += " select cInvCode from " + iLoginEx.pubDB_UT() + "..AlternativeMateriel (nolock) where cAccID='" + iLoginEx.AccID() + "' and AtnmType=0 and Disabled=0 and isMRP=1 and isTwoWay=1 and cInvCodeAtnm='" + cInvCode + "' \r\n";
if (TimeRange)
{
selectSQL += " and StartDate>='" + SoDateL + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and StartDate>='" + cDateL + "' \r\n";
}
}
selectSQL += " )altm where k.cInvCode=altm.cInvCode \r\n";
selectSQL += " ) \r\n";
selectSQL += " group by k.cInvCode,'['+c.cWhCode+']'+c.cWhName \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
}
break;
}
}
if (cType == 0 || cType == 2)
{
selectSQL = " ----*生产订单(在制)******** \r\n";
selectSQL += " \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " select '' as 'Prod_cInvCode',DocTypeNo=5,'生产订单(在制)-' +isnull(d.cDepName,'') as 'DocType' ,c.MoCode as 'cCode',u.cUser_Name as 'cpersonname',cDefine30=isnull(a.SoCode,'')+'('+convert(varchar(8),isnull(a.sortseq,0))+')',b.StartDate as 'dDate',a.InvCode as 'cInvCode',isnull(a.qty,0)-isnull(a.QualifiedInQty,0) as 'moQty', \r\n";
selectSQL += " Now_PurArrQty=0 ,Now_PurQty=0,CurSotckQty=0 , useQty=0, toArrQty=0,AltmQty=0,OsQty=0 from mom_orderdetail a (nolock) \r\n";
selectSQL += " left join mom_morder b (nolock)on a.MoId=b.MoId and a.ModId=b.ModId \r\n";
selectSQL += " left join mom_order c (nolock) on a.MoId=c.MoId \r\n";
selectSQL += " left join Department d (nolock) on d.cDepCode=a.MDeptCode \r\n";
selectSQL += " left join " + UFsystem + "..UA_User u (nolock) on c.CreateUser=u.cUser_ID \r\n";
selectSQL += " where 1=1 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and a.InvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where a.InvCode=keys.cInvCode) \r\n";
}
}
if (TimeRange)
{
selectSQL += " and b.StartDate>='" + MoStartDateL + "' and b.StartDate<='" + MoStartDateH + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and b.StartDate>='" + cDateL + "' ";
}
if (cDateH.Length > 0)
{
selectSQL += " and b.StartDate<='" + cDateH + "' ";
}
}
selectSQL += " and a.Status=3 and len(isnull(a.CloseUser,''))=0 and (isnull(a.qty,0)-isnull(a.QualifiedInQty,0))>0 \r\n";
selectSQL += " \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
}
selectSQL = " ----*销售订单******** \r\n";
selectSQL += " \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " select '' as 'Prod_cInvCode',DocTypeNo=6,'销售订单' as 'DocType',b.cSOCode+'('+convert(varchar,a.iRowNo)+')' as 'cCode',person.cpersonname,cDefine30=convert(varchar,a.iSOsID),b.dDate,a.cInvCode,moQty=0,Now_PurArrQty=0,Now_PurQty=0,CurSotckQty=0,useQty=isnull(a.iQuantity,0)-isnull(a.iFHQuantity,0), toArrQty=0,AltmQty=0,OsQty=0 \r\n";
selectSQL += " from SO_SODetails a (nolock) left join SO_SOMain b (nolock) on a.ID =b.ID \r\n";
selectSQL += " left join person (nolock) on b.cPersonCode= person.cpersoncode \r\n";
selectSQL += " where 1=1 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and a.cInvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where a.cInvCode=keys.cInvCode) \r\n";
}
}
if (TimeRange)
{
selectSQL += " and b.dDate>='" + SoDateL + "' and b.dDate<='" + SoDateH + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and b.dDate>='" + cDateL + "' ";
}
if (cDateH.Length > 0)
{
selectSQL += " and b.dDate<='" + cDateH + "' ";
}
}
selectSQL += " and isnull(b.iStatus,0)=1 and (isnull(a.iQuantity,0)-isnull(a.iFHQuantity,0))>0 and len((isnull(a.cSCloser,'')+isnull(b.cCloser,'')))=0 \r\n";
selectSQL += " \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
selectSQL = " ----*预测订单******** \r\n";
selectSQL += " \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " select '' as 'Prod_cInvCode',DocTypeNo=13,'预测订单' as 'DocType',b.cCode +'('+convert(varchar,a.iRowNo)+')' as 'cCode',person.cpersonname,cDefine30=convert(varchar,a.autoid),b.dDate,a.cInvCode,moQty=0,Now_PurArrQty=0,Now_PurQty=0,CurSotckQty=0,useQty=isnull(a.iQuantity,0)-isnull(a.fdhquantity,0), toArrQty=0,AltmQty=0,OsQty=0 \r\n";
selectSQL += " from SA_PreOrderDetails a (nolock) left join SA_PreOrderMain b (nolock) on a.ID =b.ID \r\n";
selectSQL += " left join person (nolock) on b.cPersonCode= person.cpersoncode \r\n";
selectSQL += " where 1=1 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and a.cInvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where a.cInvCode=keys.cInvCode) \r\n";
}
}
if (TimeRange)
{
selectSQL += " and b.dDate>='" + SoDateL + "' and b.dDate<='" + SoDateH + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and b.dDate>='" + cDateL + "' ";
}
if (cDateH.Length > 0)
{
selectSQL += " and b.dDate<='" + cDateH + "' ";
}
}
selectSQL += " and len(isnull(b.cVerifier ,''))>0 and (isnull(a.iQuantity,0)-isnull(a.fdhquantity,0))>0 and len((isnull(a.cSCloser,'')+isnull(b.cCloser,'')))=0 \r\n";
selectSQL += " \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
selectSQL = " \r\n";
selectSQL += " ----*生产订单(需求)******** \r\n";
selectSQL += " insert into " + ComprehensiveStockST31 + "(Prod_cInvCode,DocTypeNo,DocType,cCode,cpersonname,cDefine30,dDate,cinvcode,moQty,Now_PurArrQty,Now_PurQty,CurSotckQty,useQty,toArrQty,AltmQty,OsQty) \r\n";
selectSQL += " select t.InvCode as 'Prod_cInvCode',DocTypeNo=7,'生产订单（需求）' as 'DocType', m.mocode as 'cCode',u.cUser_Name as 'cpersonname',isnull(t.SoCode,'')+'('+convert(varchar(8),isnull(t.sortseq,0))+')' as 'cDefine30', mm.StartDate as 'dDate', \r\n";
selectSQL += " c.invcode as 'cInvCode',moQty=0,Now_PurArrQty=0,Now_PurQty=0,CurSotckQty=0,useQty=isnull(c.qty,0)-isnull(c.IssQty,0), toArrQty=0 ,AltmQty=0,OsQty=0 \r\n";
selectSQL += " from mom_moallocate c (nolock) \r\n";
selectSQL += " left join mom_orderdetail t (nolock) on c.modid=t.modid \r\n";
selectSQL += " left join mom_order m (nolock) on m.moid=t.moid \r\n";
selectSQL += " left join mom_morder mm (nolock) on mm.modid=t.modid and t.ModId=mm.ModId \r\n";
selectSQL += " left join " + UFsystem + "..UA_User u (nolock) on m.CreateUser=u.cUser_ID \r\n";
selectSQL += " where 1=1 \r\n";
if (cInvCode.Length > 0)
{
selectSQL += " and c.InvCode='" + cInvCode + "' \r\n";
}
else
{
if (iKeys.Length > 0)
{
selectSQL += " and exists (select 1 from " + iKeys + " keys where c.InvCode=keys.cInvCode) \r\n";
}
}
if (TimeRange)
{
selectSQL += " and mm.StartDate>='" + MoStartDateL + "' and mm.StartDate<='" + MoStartDateH + "' ";
}
else
{
if (cDateL.Length > 0)
{
selectSQL += " and mm.StartDate>='" + cDateL + "' ";
}
if (cDateH.Length > 0)
{
selectSQL += " and mm.StartDate<='" + cDateH + "' ";
}
}
selectSQL += " and t.Status=3 and len(isnull(t.CloseUser,''))=0 and (isnull(c.qty,0)-isnull(c.IssQty,0))>0 \r\n";
selectSQL += " \r\n";
selectSQL += " \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
selectSQL = " CREATE INDEX ComprehensiveStockST31" + iLoginEx.GetMacAddress().Replace(":", "") + "_idx ON " + ComprehensiveStockST31 + " \r\n";
selectSQL += " ( \r\n";
selectSQL += " DocTypeNo,cInvCode \r\n";
selectSQL += " ) ; \r\n";
myCommand.CommandText = selectSQL;
myCommand.ExecuteNonQuery();
if (myConn.State == System.Data.ConnectionState.Open)
{
myConn.Close();
}
return(ComprehensiveStockST31);
}
/// <summary>
/// 库存综合查询
/// </summary>
/// <param name="iLoginEx"></param>
/// <param name="cType">0=库存查询，显示所有；1=请购单导入（不仅显示在制）；2=制造单导入（仅显示现存量、在制、已分配量）；3=仅显示替代料</param>
/// <param name="cInvCode">物料编码</param>
/// <param name="cDateL">日期L</param>
/// <param name="cDateH">日期H</param>
/// <param name="UFsystem">用友中间数据库名</param>
/// <param name="cWhCode">仓库代码</param>
/// <param name="iKeys">查询条件</param>
/// <returns></returns>
public string ComprehensiveStockInfo(LoginEx iLoginEx, ushort cType, string cInvCode, string cDateL, string cDateH, string UFsystem, string cWhCode, string iKeys)
{
return(ComprehensiveStockInfo(iLoginEx, cType, cInvCode, cDateL, cDateH, UFsystem, cWhCode, iKeys, "", 1, false));
}
public IActionResult LoginExternal(string loginEx)
{
Account createdAccount = null;
UserProfile userProfile = null;
LoginEx login = JsonConvert.DeserializeObject <LoginEx>(loginEx);
if (login.Email == null)
{
login.Email = "customer_" + Encryptor.RandomString(6) + "@petshop.com";
}
UserProfile profile = GetApiUserProfile.GetUserProfiles().SingleOrDefault(p => p.UserProfileEmail == login.Email);
// create if null
if (profile == null)
{
string passwordTemp = Encryptor.RandomString(12);
// create account
RegisterModel register = new RegisterModel()
{
Email = login.Email,
FirstName = login.FirstName,
MiddleName = login.MiddleName,
LastName = login.LastName,
Password = passwordTemp,
IsLoginExternal = true,
DOB = "1990/1/1",
Avatar = "noimage.png"
};
createdAccount = CreateAccount(register);
// create profile
userProfile = CreateProfile(createdAccount, register);
// create user score
CreateUserScore(userProfile);
}
// request token login
using (HttpClient client = new HttpClient())
{
client.BaseAddress = new Uri(Constants.BASE_URI);
var postTask = client.PostAsJsonAsync <LoginEx>("LoginAuthentication/AuthenticateExternal", login);
postTask.Wait();
var result = postTask.Result;
if (result.IsSuccessStatusCode)
{
var readTask = result.Content.ReadAsAsync <CredentialModel>();
readTask.Wait();
CredentialModel response = readTask.Result;
// get user profile
UserProfile res_profile = GetApiUserProfile.GetUserProfiles().SingleOrDefault(p => p.AccountId == Convert.ToInt32(response.AccountId));
// get account && role
Account account = GetApiAccounts.GetAccounts().SingleOrDefault(p => p.AccountId == profile.AccountId);
account.AccountRole = GetApiAccountRoles.GetAccountRoles().SingleOrDefault(p => p.AccountRoleId == account.AccountRoleId);
profile.Account = account;
response.Profile = profile;
// set 1 session for credential
HttpContext.Session.SetObject("vm", response);
return(RedirectToAction("Index", "Home"));
}
else
{
return(View());
}
}
}
public frmcInvCCodeTree(LoginEx iiLoginEx)
{
InitializeComponent();
iLoginEx = iiLoginEx;
}
