public SqlDataReader GetWarehouseBycWhCode(WarehouseInfo model) { string strSql = string.Empty; strSql = string.Format("SELECT cwhcode,cwhname FROM Warehouse WHERE cwhcode = '{0}'", model.WarehouseNo); return(OperationSql.ExecuteReaderForU8(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.ExecuteReaderForU8(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.ExecuteReaderForU8(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 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.ExecuteReaderForU8(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); } }