public SqlDataReader GetWarehouseBycWhCode(WarehouseInfo model)
{
string strSql = string.Empty;
strSql = string.Format("SELECT cwhcode,cwhname FROM Warehouse WHERE cwhcode = '{0}'", model.WarehouseNo);
return(OperationSql.ExecuteReaderForERP(CommandType.Text, strSql, null));
}
public bool GetSaleBillVouchByCode(string code, out SaleBillVouch_Model model, out string strErrMsg)
{
try
{
string strSql = @"Select SaleBillVouchZW.cSOCode as ssocode,csbvcode as ssbvcode, SaleBillVouchZW.sbvid, SaleBillVouchZW.autoid,SaleBillVouchZW.SBVID,SO_SODetails.iquantity as ssoqty,SaleBillVouchZW.iquantity as ssbvqty,cWhCode,cWhName,SaleBillVouchZW.cInvCode,SaleBillVouchZW.cinvname,cinvstd,SaleBillVouchZW.irowno as ssbvrowno,SO_SODetails.iRowNo as ssorowno,cbdefine1
From SaleBillVouchZW
left join (select cbdefine1 as cbdefine1,cbdefine5 as cbdefine5,cbdefine6 as cbdefine6,autoid as keyextend_b_autoid_salebillvouchs_extradefine_autoid
from salebillvouchs_extradefine) extend_b_autoid_salebillvouchs_extradefine on
keyextend_b_autoid_salebillvouchs_extradefine_autoid=salebillvouchzw.autoid
join SaleBillVouchZT on SaleBillVouchZT.sbvid=SaleBillVouchZW.sbvid
left join SO_SODetails on (SaleBillVouchZW.cSOCode = SO_SODetails.cSOCode and SaleBillVouchZW.iorderrowno = SO_SODetails.iRowNo)
where cSource=N'销售' and csbvcode='" + code + "'";
model = new SaleBillVouch_Model();
model.csbvcode = code;
model.details = new List <SaleBillDetails_Model>();
using (SqlDataReader dr = OperationSql.ExecuteReaderForERP(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
model.sbvid = dr["SBVID"].ToDBString();
SaleBillDetails_Model SM = new SaleBillDetails_Model();
SM.autoid = dr["autoid"].ToDBString();
SM.cinvcode = dr["cinvcode"].ToDBString();
SM.cinvname = dr["cinvname"].ToDBString();
SM.cinvstd = dr["cinvstd"].ToDBString();
SM.ssorowno = dr["ssorowno"].ToDBString();
SM.ssbvrowno = dr["ssbvrowno"].ToDBString();
SM.cWhCode = dr["cWhCode"].ToDBString();
SM.cWhName = dr["cWhName"].ToDBString();
SM.ssoqty = dr["ssoqty"].ToDecimal();
SM.ssbvqty = dr["ssbvqty"].ToDecimal();
SM.ssocode = dr["ssocode"].ToDBString();
SM.ssbvcode = dr["ssbvcode"].ToDBString();
model.details.Add(SM);
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
model = null;
return(false);
}
}
//internal bool DealImport(ref string strError)
//{
// if (!UpdateSapMaterial(ref strError)) return false;
// strError = string.Empty;
// //SqlParameter[] para = new SqlParameter[]{
// //new SqlParameter("ErrStrng",SqlDbType.NVarChar,200,ParameterDirection.Output,true,0,0,"ErrStrng",DataRowVersion.Default,strError)
// //};
// //OperationSql.ExecuteNonQuery2( CommandType.StoredProcedure, "Proc_ImportStock", para);
// //strError = para[para.Length - 1].Value.ToString();
// if (string.IsNullOrEmpty(strError))
// {
// return true;
// }
// else if (strError.ToLower() == "null")
// {
// return true;
// }
// else
// {
// return false;
// }
//}
//private bool UpdateSapMaterial(ref string strError)
//{
// List<Barcode_Model> lstBarcode = new List<Barcode_Model>();
// using (SqlDataReader dr = OperationSql.ExecuteReader(CommandType.Text, "SELECT MATERIALNO FROM T_P_IMPORTSTOCK WHERE MATERIALDESC IS NULL AND ROWNUM <= 5000 GROUP BY MATERIALNO", null))
// {
// while (dr.Read())
// {
// lstBarcode.Add(new Barcode_Model() { MATERIALNO = dr["MATERIALNO"].ToDBString() });
// }
// dr.Close();
// }
// if (lstBarcode == null || lstBarcode.Count <= 0) return true;
// List<Material_Model> lstMaterial = new List<Material_Model>();
// Material_SAP materialSap = new Material_SAP();
// if (!materialSap.GetMaterialInfoForSAPByBarcode(lstBarcode, ref lstMaterial, ref strError)) return false;
// if (lstMaterial == null || lstMaterial.Count <= 0) return false;
// List<string> lstSql = new List<string>();
// foreach (Material_Model material in lstMaterial)
// {
// lstSql.Add(string.Format("UPDATE T_P_IMPORTSTOCK SET MATERIALDESC = '{0}', ISROHS = {1} WHERE MATERIALNO = '{2}'", material.MaterialDesc, material.ROHS == "ROHS" ? 2 : 1, material.MaterialNo));
// }
// if (lstSql == null || lstSql.Count <= 0) return true;
// int OnceImportSize = 500;
// List<string> lstTemp = new List<string>();
// while (lstSql.Count >= OnceImportSize)
// {
// lstTemp =(lstSql.GetRange(0, OnceImportSize-1));
// if (OperationSql.ExecuteNonQueryList(lstTemp, ref strError) <=0)
// {
// return false;
// }
// lstSql.RemoveRange(0, OnceImportSize - 1);
// }
// if (lstSql.Count >= 1)
// {
// lstTemp = lstSql;
// if (OperationSql.ExecuteNonQueryList(lstTemp, ref strError) <= 0)
// {
// return false;
// }
// }
// object o = OperationSql.ExecuteScalar(CommandType.Text, "select count(1) from (SELECT MATERIALNO FROM T_P_IMPORTSTOCK WHERE MATERIALDESC IS NULL GROUP BY MATERIALNO )", null);
// if (o.ToInt32() >= 1)
// {
// strError = "还有未更新完的物料信息";
// return false;
// }
// return true;
//}
public bool GetSaleBillVouchCodeByCustomer(string ccusname, out List <string> list, out string strErrMsg)
{
try
{
list = new List <string>();
string strSql = "select csbvcode from SaleBillVouchZT where ccusname like '%" + ccusname + "%' and cSource=N'销售' and ivouchstate='Opening' and iverifystate=0 and isnull(bReturnFlag,0)<>1 ";
using (SqlDataReader dr = OperationSql.ExecuteReaderForERP(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
list.Add(dr["csbvcode"].ToDBString());
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
list = null;
return(false);
}
}
public SqlDataReader ReadData(ReadApiType iType, params object[] list)
{
string strSql = string.Empty;
int iNo = Convert.ToInt32(iType);
switch (iNo)
{
case 1:
if (list != null && list.Count <object>() > 0)
{
strSql = string.Format("select top 100000000 * from xMES_ItemMST where invtid='{0}'", list[0].ToString());
}
break;
case 2:
if (list != null && list.Count <object>() > 0)
{
strSql = string.Format(@"select top 100000000 a.*,b.Allergic,b.SceneMaterial,b.UserFlag,b.InvtType,b.ShelfLife,b.StkUnit from xMES_Purchase a
join xMES_ItemMST b on a.invtid=b.invtid where PoNbr='{0}'", list[0].ToString());
}
break;
case 3:
if (list != null && list.Count <object>() > 0)
{
int iCount = list.Count <object>();
if (iCount == 1)
{
strSql = string.Format(@"select top 100000000 a.BatNbr,a.SiteID,a.ToSiteID,a.RefNbr,a.InvtID,a.CHDesc,a.Descr,a.WhseLoc,a.ToWhseLoc,a.LotSerNbr,a.Qty,a.TranDate
,b.Allergic,b.SceneMaterial,b.UserFlag,b.InvtType,b.ShelfLife,b.StkUnit from xMES_TranSF_To01 a join join xMES_ItemMST b on a.invtid=b.invtid where InvtID='{0}'", list[0].ToString());
}
if (iCount == 2)
{
strSql = string.Format(@"select top 100000000 a.BatNbr,a.SiteID,a.ToSiteID,a.RefNbr,a.InvtID,a.CHDesc,a.Descr,a.WhseLoc,a.ToWhseLoc,a.LotSerNbr,a.Qty,a.TranDate
,b.Allergic,b.SceneMaterial,b.UserFlag,b.InvtType,b.ShelfLife,b.StkUnit from xMES_TranSF_To01 a join join xMES_ItemMST b on a.invtid=b.invtid where InvtID='{0}' and LotSerNbr='{1}'", list[0].ToString(), list[1].ToString());
}
if (iCount == 3)
{
strSql = string.Format(@"select top 100000000 a.BatNbr,a.SiteID,a.ToSiteID,a.RefNbr,a.InvtID,a.CHDesc,a.Descr,a.WhseLoc,a.ToWhseLoc,a.LotSerNbr,a.Qty,a.TranDate
,b.Allergic,b.SceneMaterial,b.UserFlag,b.InvtType,b.ShelfLife,b.StkUnit from xMES_TranSF_To01 a join join xMES_ItemMST b on a.invtid=b.invtid where InvtID='{0}' and LotSerNbr='{1}' and CONVERT(varchar(100), TranDate, 23)='{2}'", list[0].ToString(), list[1].ToString(), Convert.ToDateTime(list[2]).ToString("yyyy-MM-dd"));
}
if (iCount == 4)
{
strSql = string.Format(@"select top 100000000 a.BatNbr,a.SiteID,a.ToSiteID,a.RefNbr,a.InvtID,a.CHDesc,a.Descr,a.WhseLoc,a.ToWhseLoc,a.LotSerNbr,a.Qty,a.TranDate
,b.Allergic,b.SceneMaterial,b.UserFlag,b.InvtType,b.ShelfLife,b.StkUnit from xMES_TranSF_To01 a join join xMES_ItemMST b on a.invtid=b.invtid where InvtID='{0}' and LotSerNbr='{1}' and TranDate>= '{2}' and and TranDate<='{3}'", list[0].ToString(), list[1].ToString(), Convert.ToDateTime(list[2]).ToString("yyyy-MM-dd"), Convert.ToDateTime(list[3]).ToString("yyyy-MM-dd"));
}
}
break;
case 4:
if (list != null && list.Count <object>() > 0)
{
strSql = string.Format("select top 100000000 * from xMES_BOM where InvtID='{0}'", list[0].ToString());
}
break;
case 5:
if (list != null && list.Count <object>() > 0)
{
int iCount = list.Count <object>();
if (iCount == 1)
{
strSql = string.Format("select top 100000000 * from xMES_PackingSlip where ShipperID='{0}'", list[0].ToString());
}
if (iCount == 2)
{
strSql = string.Format("select top 100000000 * from xMES_PackingSlip where ShipperID='{0}' and CONVERT(varchar(100),ShipDateAct, 23)='{1}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"));
}
}
break;
case 6:
if (list != null && list.Count <object>() > 0)
{
int iCount = list.Count <object>();
if (iCount == 1)
{
strSql = string.Format("select top 100000000 * from xMES_Prod a join xMES_ItemMST b on a.invtid=b.invtid where ProdOrdID='{0}'", list[0].ToString());
}
if (iCount == 2)
{
strSql = string.Format("select top 100000000 * from xMES_Prod a join xMES_ItemMST b on a.invtid=b.invtid where ProdOrdID='{0}' and CONVERT(varchar(100),StartDate, 23)='{1}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"));
}
if (iCount == 3)
{
strSql = string.Format("select top 100000000 * from xMES_Prod a join xMES_ItemMST b on a.invtid=b.invtid where ProdOrdID='{0}' and CONVERT(varchar(100),StartDate, 23)='{1}' and CONVERT(varchar(100),EndDate, 23)='{2}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"), Convert.ToDateTime(list[2]).ToString("yyyy-MM-dd"));
}
}
break;
case 7:
if (list != null && list.Count <object>() > 0)
{
int iCount = list.Count <object>();
if (iCount == 1)
{
strSql = string.Format("select top 100000000 * from xMES_TranSF_To03 where InvtID='{0}'", list[0].ToString());
}
if (iCount == 2)
{
strSql = string.Format("select top 100000000 * from xMES_TranSF_To03 where InvtID='{0}' and LotSerNbr='{1}'", list[0].ToString(), list[1].ToString());
}
if (iCount == 3)
{
strSql = string.Format("select top 100000000 * from xMES_TranSF_To03 where InvtID='{0}' and LotSerNbr='{1}' and CONVERT(varchar(100), TranDate, 23)='{2}'", list[0].ToString(), list[1].ToString(), Convert.ToDateTime(list[2]).ToString("yyyy-MM-dd"));
}
}
break;
case 8:
if (list != null && list.Count <object>() > 0)
{
int iCount = list.Count <object>();
if (iCount == 1)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_1 where ProdOrdID='{0}'", list[0].ToString());
}
if (iCount == 2)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_1 where ProdOrdID='{0}' and CONVERT(varchar(100),StartDate, 23)='{1}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"));
}
if (iCount == 3)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_1 where ProdOrdID='{0}' and CONVERT(varchar(100),StartDate, 23)='{1}' and CONVERT(varchar(100),EndDate, 23)='{2}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"), Convert.ToDateTime(list[2]).ToString("yyyy-MM-dd"));
}
}
break;
case 9:
if (list != null && list.Count <object>() > 0)
{
int iCount = list.Count <object>();
if (iCount == 1)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_2 where ProdOrdID='{0}'", list[0].ToString());
}
if (iCount == 2)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_2 where ProdOrdID='{0}' and CONVERT(varchar(100),StartDate, 23)='{1}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"));
}
if (iCount == 3)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_2 where ProdOrdID='{0}' and CONVERT(varchar(100),StartDate, 23)='{1}' and CONVERT(varchar(100),EndDate, 23)='{2}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"), Convert.ToDateTime(list[2]).ToString("yyyy-MM-dd"));
}
}
break;
case 10:
if (list != null && list.Count <object>() > 0)
{
int iCount = list.Count <object>();
if (iCount == 1)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_3 where ProdOrdID='{0}'", list[0].ToString());
}
if (iCount == 2)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_3 where ProdOrdID='{0}' and CONVERT(varchar(100),StartDate, 23)='{1}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"));
}
if (iCount == 3)
{
strSql = string.Format("select top 100000000 * from xMES_ProdBook_3 where ProdOrdID='{0}' and CONVERT(varchar(100),StartDate, 23)='{1}' and CONVERT(varchar(100),EndDate, 23)='{2}'", list[0].ToString(), Convert.ToDateTime(list[1]).ToString("yyyy-MM-dd"), Convert.ToDateTime(list[2]).ToString("yyyy-MM-dd"));
}
}
break;
case 11:
if (list != null && list.Count <object>() > 0)
{
int iCount = list.Count <object>();
if (iCount == 1)
{
strSql = string.Format("select top 100000000 * from xMES_TranSF_To04 where InvtID='{0}'", list[0].ToString());
}
if (iCount == 2)
{
strSql = string.Format("select top 100000000 * from xMES_TranSF_To04 where InvtID='{0}' and LotSerNbr='{1}'", list[0].ToString(), list[1].ToString());
}
if (iCount == 3)
{
strSql = string.Format("select top 100000000 * from xMES_TranSF_To04 where InvtID='{0}' and LotSerNbr='{1}' and CONVERT(varchar(100), TranDate, 23)='{2}'", list[0].ToString(), list[1].ToString(), Convert.ToDateTime(list[2]).ToString("yyyy-MM-dd"));
}
}
break;
}
return(OperationSql.ExecuteReaderForERP(CommandType.Text, strSql, null));
}
public bool GetSaleBillDetailsForTrans(SaleBillDetails_Model detail, out List <SaleBillDetails_Model> list, out string strErrMsg)
{
try
{
list = new List <SaleBillDetails_Model>();
string strSql = @"select cSOCode,iRowNo,iQuantity from SO_SODetails
where dbclosedate is null and cInvCode = '" + detail.cinvcode + "' and cSOCode != '" + detail.ssocode + "'";
using (SqlDataReader dr = OperationSql.ExecuteReaderForERP(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
strSql = "select * from T_temptrans where RealQty < " + detail.qty + " and ssocode = '" + dr["cSOCode"].ToDBString() + "' and cinvcode = '" + detail.cinvcode + "'";
if (OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql).Read())
{
break;
}
strSql = "select SUM(qty) from T_temptrans where cinvcode = '" + detail.cinvcode + "' and dsocode = '" + dr["cSOCode"].ToDBString() + "'";
decimal sum = OperationSql.ExecuteScalar(System.Data.CommandType.Text, strSql).ToDecimal();
if (sum != null && (dr["iQuantity"].ToDecimal() - sum < detail.qty))
{
continue;
}
strSql = "select SUM(T_STOCK.qty) from T_OUTBARCODE join T_STOCK on T_STOCK.serialno = T_OUTBARCODE.serialno where T_STOCK.warehouseno = '14' and T_OUTBARCODE.materialno = '" + detail.cinvcode + "' and SoCode = '" + dr["cSOCode"].ToDBString() + "'";
sum = OperationSql.ExecuteScalar(System.Data.CommandType.Text, strSql).ToDecimal();
if (sum == null)
{
continue;
}
else if (sum < detail.qty)
{
continue;
}
SaleBillDetails_Model SM = new SaleBillDetails_Model();
SM.autoid = detail.autoid;
SM.cinvcode = detail.cinvcode;
SM.cinvname = detail.cinvname;
SM.cinvstd = detail.cinvstd;
SM.ssorowno = detail.ssorowno;
SM.ssbvrowno = detail.ssbvrowno;
SM.cWhCode = detail.cWhCode;
SM.cWhName = detail.cWhName;
SM.ssoqty = detail.ssoqty;
SM.ssbvqty = detail.ssbvqty;
SM.ssocode = detail.ssocode;
SM.ssbvcode = detail.ssbvcode;
SM.dsocode = dr["cSOCode"].ToDBString();
SM.dsorowno = dr["iRowNo"].ToDBString();
SM.dsoqty = sum;
SM.qty = detail.qty;
//SM.dsoqty = dr["iQuantity"].ToDecimal();
list.Add(SM);
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
strErrMsg = ex.Message;
list = null;
return(false);
}
}