public static DataTable GetProviderSelect(ProviderInfoModel ProviderInfoM)
{
return ProviderInfoDBHelper.GetProviderSelect(ProviderInfoM);
}
//public static bool UpdateProviderInfo(ProviderInfoModel model, string no)
public static bool UpdateProviderInfo(ProviderInfoModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
if (model.ID <= 0)
{
return false;
}
try
{
bool succ = false;
LogInfoModel logModel = InitLogInfo(model.CustNo);
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//设置模块ID 模块ID请在ConstUtil中定义，以便维护
logModel.ModuleID = ConstUtil.MODULE_ID_PROVIDERINFO_ADD;
succ = ProviderInfoDBHelper.UpdateProviderInfo(model);
if (!succ)
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
else
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
LogDBHelper.InsertLog(logModel);
return succ;
}
catch (System.Exception ex)
{
throw ex;
}
}
public static DataTable GetProviderInfo(ProviderInfoModel ProviderInfoM, int pageIndex, int pageCount, string OrderBy, out int totalCount)
{
try
{
return ProviderInfoDBHelper.GetProviderInfo(ProviderInfoM,pageIndex,pageCount,OrderBy,out totalCount);
}
catch (Exception ex)
{
throw ex;
}
}
public static bool InsertProviderInfo(ProviderInfoModel model, out string ID)
{
try
{
bool succ = false;
LogInfoModel logModel = InitLogInfo(model.CustNo);
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//设置模块ID 模块ID请在ConstUtil中定义，以便维护
logModel.ModuleID = ConstUtil.MODULE_ID_PROVIDERINFO_ADD;
succ = ProviderInfoDBHelper.InsertProviderInfo(model, out ID);
if (!succ)
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
else
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
LogDBHelper.InsertLog(logModel);
return succ;
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取供应商及交货、运送、结算
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetProviderSelect(ProviderInfoModel ProviderInfoM)
{
SqlCommand comm = new SqlCommand();
string sql = "select a.ID as ProviderID ,a.CustNo as ProviderNo,a.CustName as ProviderName,a.TakeType as TakeType,isnull(b.TypeName,'') as TakeTypeName,a.CarryType as CarryType,isnull(c.TypeName,'') as CarryTypeName,a.PayType as PayType,isnull(d.TypeName,'') as PayTypeName from officedba.ProviderInfo as a left join officedba.codepublictype as b on b.id = a.TakeType and b.CompanyCD=a.CompanyCD left join officedba.codepublictype as c on c.id = a.CarryType left join officedba.codepublictype as d on d.id = a.PayType where a.UsedStatus =1 AND a.CompanyCD=@CompanyCD1 ";
if (!string.IsNullOrEmpty(ProviderInfoM.CustNo))
{
sql += " and a.CustNo like @ProviderNo";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderNo", "%" + ProviderInfoM.CustNo + "%"));
}
if (!string.IsNullOrEmpty(ProviderInfoM.CustName))
{
sql += " and a.CustName like @ProviderName";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderName", "%" + ProviderInfoM.CustName + "%"));
}
if (!string.IsNullOrEmpty(ProviderInfoM.CompanyCD))
{
sql += " and a.CompanyCD= @CompanyCD";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", ProviderInfoM.CompanyCD));
}
if (!string.IsNullOrEmpty(ProviderInfoM.CompanyCD))
{
sql += " and a.UsedStatus=@UsedStatus";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", ProviderInfoM.UsedStatus));
}
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD1", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD));
comm.CommandText = sql;
return SqlHelper.ExecuteSearch(comm);
}
public static DataTable GetProviderInfo(ProviderInfoModel ProviderInfoM, int pageIndex, int pageCount, string OrderBy, out int totalCount)
{
SqlParameter[] parms = new SqlParameter[7];
int i = 0;
parms[i++] = new SqlParameter("@CompanyCD", ProviderInfoM.CompanyCD);
parms[i++] = new SqlParameter("@ProviderNo", ProviderInfoM.CustNo);
parms[i++] = new SqlParameter("@ProviderName", ProviderInfoM.CustName);
parms[i++] = new SqlParameter("@pageIndex", pageIndex);
parms[i++] = new SqlParameter("@pageCount", pageCount);
parms[i++] = new SqlParameter("@OrderBy", OrderBy);
parms[i++] = MakeParam("@totalCount", SqlDbType.Int, 0, ParameterDirection.Output, null);
DataTable dt = SqlHelper.ExecuteStoredProcedure("[officedba].[ProcGetProviderInfo]", parms);
totalCount = int.Parse(parms[6].Value.ToString());
return dt;
}
public static bool UpdateProviderInfo(ProviderInfoModel model)
{
if (model.ID <= 0)
{
return false;
}
ArrayList listADD = new ArrayList();
bool result = false;
#region 修改供应商档案
StringBuilder sqlArrive = new StringBuilder();
sqlArrive.AppendLine("Update Officedba.ProviderInfo set CompanyCD=@CompanyCD,");
sqlArrive.AppendLine("BigType=@BigType,CustType=@CustType,CustClass=@CustClass,CustNo=@CustNo,CustName=@CustName,");
sqlArrive.AppendLine("CustNam=@CustNam,PYShort=@PYShort,CreditGrade=@CreditGrade,CustNote=@CustNote,Manager=@Manager,");
sqlArrive.AppendLine("AreaID=@AreaID,LinkCycle=@LinkCycle,HotIs=@HotIs,HotHow=@HotHow,MeritGrade=@MeritGrade,");
sqlArrive.AppendLine("CompanyType=@CompanyType,StaffCount=@StaffCount,SetupDate=@SetupDate,ArtiPerson=@ArtiPerson,SetupMoney=@SetupMoney,");
sqlArrive.AppendLine("SetupAddress=@SetupAddress,CapitalScale=@CapitalScale,SaleroomY=@SaleroomY,ProfitY=@ProfitY,TaxCD=@TaxCD,");
sqlArrive.AppendLine("BusiNumber=@BusiNumber,IsTax=@IsTax,SellArea=@SellArea,CountryID=@CountryID,Province=@Province,");
sqlArrive.AppendLine("City=@City,SendAddress=@SendAddress,Post=@Post,ContactName=@ContactName,Tel=@Tel,Fax=@Fax,");
sqlArrive.AppendLine("Mobile=@Mobile,email=@email,OnLine=@OnLine,WebSite=@WebSite,TakeType=@TakeType,");
sqlArrive.AppendLine("CarryType=@CarryType,PayType=@PayType,CurrencyType=@CurrencyType,OpenBank=@OpenBank,AccountMan=@AccountMan,");
sqlArrive.AppendLine("AccountNum=@AccountNum,Remark=@Remark,UsedStatus=@UsedStatus,CreateDate=@CreateDate,");
sqlArrive.AppendLine("ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID,AllowDefaultDays=@AllowDefaultDays where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@BigType", 2));
comm.Parameters.Add(SqlHelper.GetParameter("@CustType", model.CustType));
comm.Parameters.Add(SqlHelper.GetParameter("@CustClass", model.CustClass));
comm.Parameters.Add(SqlHelper.GetParameter("@CustNo", model.CustNo));
comm.Parameters.Add(SqlHelper.GetParameter("@CustName", model.CustName));
comm.Parameters.Add(SqlHelper.GetParameter("@CustNam", model.CustNam));
comm.Parameters.Add(SqlHelper.GetParameter("@PYShort", model.PYShort));
comm.Parameters.Add(SqlHelper.GetParameter("@CreditGrade", model.CreditGrade));
comm.Parameters.Add(SqlHelper.GetParameter("@CustNote", model.CustNote));
comm.Parameters.Add(SqlHelper.GetParameter("@Manager", model.Manager));
comm.Parameters.Add(SqlHelper.GetParameter("@AreaID", model.AreaID));
comm.Parameters.Add(SqlHelper.GetParameter("@LinkCycle", model.LinkCycle));
comm.Parameters.Add(SqlHelper.GetParameter("@HotIs", model.HotIs));
comm.Parameters.Add(SqlHelper.GetParameter("@HotHow", model.HotHow));
comm.Parameters.Add(SqlHelper.GetParameter("@MeritGrade", model.MeritGrade));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyType", model.CompanyType));
comm.Parameters.Add(SqlHelper.GetParameter("@StaffCount", model.StaffCount));
comm.Parameters.Add(SqlHelper.GetParameter("@SetupDate", model.SetupDate == null? SqlDateTime.Null : SqlDateTime.Parse(model.SetupDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@ArtiPerson", model.ArtiPerson));
comm.Parameters.Add(SqlHelper.GetParameter("@SetupMoney", model.SetupMoney));
comm.Parameters.Add(SqlHelper.GetParameter("@SetupAddress", model.SetupAddress));
comm.Parameters.Add(SqlHelper.GetParameter("@CapitalScale", model.CapitalScale));
comm.Parameters.Add(SqlHelper.GetParameter("@SaleroomY", model.SaleroomY));
comm.Parameters.Add(SqlHelper.GetParameter("@ProfitY", model.ProfitY));
comm.Parameters.Add(SqlHelper.GetParameter("@TaxCD", model.TaxCD));
comm.Parameters.Add(SqlHelper.GetParameter("@BusiNumber", model.BusiNumber));
comm.Parameters.Add(SqlHelper.GetParameter("@IsTax", model.isTax));
comm.Parameters.Add(SqlHelper.GetParameter("@SellArea", model.SellArea));
comm.Parameters.Add(SqlHelper.GetParameter("@CountryID", model.CountryID));
comm.Parameters.Add(SqlHelper.GetParameter("@Province", model.Province));
comm.Parameters.Add(SqlHelper.GetParameter("@City", model.City));
comm.Parameters.Add(SqlHelper.GetParameter("@SendAddress", model.SendAddress));
comm.Parameters.Add(SqlHelper.GetParameter("@Post", model.Post));
comm.Parameters.Add(SqlHelper.GetParameter("@ContactName", model.ContactName));
comm.Parameters.Add(SqlHelper.GetParameter("@Tel", model.Tel));
comm.Parameters.Add(SqlHelper.GetParameter("@Fax", model.Fax));
comm.Parameters.Add(SqlHelper.GetParameter("@Mobile", model.Mobile));
comm.Parameters.Add(SqlHelper.GetParameter("@email", model.email));
comm.Parameters.Add(SqlHelper.GetParameter("@OnLine", model.OnLine));
comm.Parameters.Add(SqlHelper.GetParameter("@WebSite", model.WebSite));
comm.Parameters.Add(SqlHelper.GetParameter("@TakeType", model.TakeType));
comm.Parameters.Add(SqlHelper.GetParameter("@CarryType", model.CarryType));
comm.Parameters.Add(SqlHelper.GetParameter("@PayType", model.PayType));
comm.Parameters.Add(SqlHelper.GetParameter("@CurrencyType", model.CurrencyType));
comm.Parameters.Add(SqlHelper.GetParameter("@OpenBank", model.OpenBank));
comm.Parameters.Add(SqlHelper.GetParameter("@AccountMan", model.AccountMan));
comm.Parameters.Add(SqlHelper.GetParameter("@AccountNum", model.AccountNum));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", model.UsedStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate == null? SqlDateTime.Null : SqlDateTime.Parse(model.CreateDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@AllowDefaultDays", model.AllowDefaultDays));
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.CommandText = sqlArrive.ToString();
listADD.Add(comm);
#endregion
try
{
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
result = true;
}
return result;
}
catch (Exception ex)
{
throw ex;
}
}
public static bool InsertProviderInfo(ProviderInfoModel model, out string ID)
{
ArrayList listADD = new ArrayList();
bool result = false;
ID = "0";
#region 采购供应商档案添加SQL语句
StringBuilder sqlArrive = new StringBuilder();
sqlArrive.AppendLine("INSERT INTO officedba.ProviderInfo");
sqlArrive.AppendLine("(CompanyCD,BigType,CustType,CustClass,CustNo,CustName,CustNam,PYShort,CreditGrade,CustNote,");
sqlArrive.AppendLine("Manager,AreaID,LinkCycle,HotIs,HotHow,MeritGrade,CompanyType,StaffCount,SetupDate,ArtiPerson,");
sqlArrive.AppendLine("SetupMoney,SetupAddress,CapitalScale,SaleroomY,ProfitY,TaxCD,BusiNumber,IsTax,SellArea,CountryID,");
sqlArrive.AppendLine("Province,City,SendAddress,Post,ContactName,Tel,Fax,Mobile,email,OnLine,");
sqlArrive.AppendLine("WebSite,TakeType,CarryType,PayType,CurrencyType,OpenBank,AccountMan,AccountNum,Remark,UsedStatus,");
sqlArrive.AppendLine("Creator,CreateDate,ModifiedDate,ModifiedUserID,AllowDefaultDays)");
sqlArrive.AppendLine("VALUES (@CompanyCD,@BigType,@CustType,@CustClass,@CustNo,@CustName,@CustNam,@PYShort,@CreditGrade,@CustNote,");
sqlArrive.AppendLine("@Manager,@AreaID,@LinkCycle,@HotIs,@HotHow,@MeritGrade,@CompanyType,@StaffCount,@SetupDate,@ArtiPerson,");
sqlArrive.AppendLine("@SetupMoney,@SetupAddress,@CapitalScale,@SaleroomY,@ProfitY,@TaxCD,@BusiNumber,@IsTax,@SellArea,@CountryID,");
sqlArrive.AppendLine("@Province,@City,@SendAddress,@Post,@ContactName,@Tel,@Fax,@Mobile,@email,@OnLine,");
sqlArrive.AppendLine("@WebSite,@TakeType,@CarryType,@PayType,@CurrencyType,@OpenBank,@AccountMan,@AccountNum,@Remark,@UsedStatus,");
sqlArrive.AppendLine("@Creator,@CreateDate,getdate(),@ModifiedUserID,@AllowDefaultDays)");
sqlArrive.AppendLine("set @ID=@@IDENTITY");
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@BigType", 2));
comm.Parameters.Add(SqlHelper.GetParameter("@CustType", model.CustType));
comm.Parameters.Add(SqlHelper.GetParameter("@CustClass", model.CustClass));
comm.Parameters.Add(SqlHelper.GetParameter("@CustNo", model.CustNo));
comm.Parameters.Add(SqlHelper.GetParameter("@CustName", model.CustName));
comm.Parameters.Add(SqlHelper.GetParameter("@CustNam", model.CustNam));
comm.Parameters.Add(SqlHelper.GetParameter("@PYShort", model.PYShort));
comm.Parameters.Add(SqlHelper.GetParameter("@CreditGrade", model.CreditGrade));
comm.Parameters.Add(SqlHelper.GetParameter("@CustNote", model.CustNote));
comm.Parameters.Add(SqlHelper.GetParameter("@Manager", model.Manager));
comm.Parameters.Add(SqlHelper.GetParameter("@AreaID", model.AreaID));
comm.Parameters.Add(SqlHelper.GetParameter("@LinkCycle", model.LinkCycle));
comm.Parameters.Add(SqlHelper.GetParameter("@HotIs", model.HotIs));
comm.Parameters.Add(SqlHelper.GetParameter("@HotHow", model.HotHow));
comm.Parameters.Add(SqlHelper.GetParameter("@MeritGrade", model.MeritGrade));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyType", model.CompanyType));
comm.Parameters.Add(SqlHelper.GetParameter("@StaffCount", model.StaffCount));
comm.Parameters.Add(SqlHelper.GetParameter("@SetupDate", model.SetupDate == null ? SqlDateTime.Null : SqlDateTime.Parse(model.SetupDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@ArtiPerson", model.ArtiPerson));
comm.Parameters.Add(SqlHelper.GetParameter("@SetupMoney", model.SetupMoney));
comm.Parameters.Add(SqlHelper.GetParameter("@SetupAddress", model.SetupAddress));
comm.Parameters.Add(SqlHelper.GetParameter("@CapitalScale", model.CapitalScale));
comm.Parameters.Add(SqlHelper.GetParameter("@SaleroomY", model.SaleroomY));
comm.Parameters.Add(SqlHelper.GetParameter("@ProfitY", model.ProfitY));
comm.Parameters.Add(SqlHelper.GetParameter("@TaxCD", model.TaxCD));
comm.Parameters.Add(SqlHelper.GetParameter("@BusiNumber", model.BusiNumber));
comm.Parameters.Add(SqlHelper.GetParameter("@IsTax", model.isTax));
comm.Parameters.Add(SqlHelper.GetParameter("@SellArea", model.SellArea));
comm.Parameters.Add(SqlHelper.GetParameter("@CountryID", model.CountryID));
comm.Parameters.Add(SqlHelper.GetParameter("@Province", model.Province));
comm.Parameters.Add(SqlHelper.GetParameter("@City", model.City));
comm.Parameters.Add(SqlHelper.GetParameter("@SendAddress", model.SendAddress));
comm.Parameters.Add(SqlHelper.GetParameter("@Post", model.Post));
comm.Parameters.Add(SqlHelper.GetParameter("@ContactName", model.ContactName));
comm.Parameters.Add(SqlHelper.GetParameter("@Tel", model.Tel));
comm.Parameters.Add(SqlHelper.GetParameter("@Fax", model.Fax));
comm.Parameters.Add(SqlHelper.GetParameter("@Mobile", model.Mobile));
comm.Parameters.Add(SqlHelper.GetParameter("@email", model.email));
comm.Parameters.Add(SqlHelper.GetParameter("@OnLine", model.OnLine));
comm.Parameters.Add(SqlHelper.GetParameter("@WebSite", model.WebSite));
comm.Parameters.Add(SqlHelper.GetParameter("@TakeType", model.TakeType));
comm.Parameters.Add(SqlHelper.GetParameter("@CarryType", model.CarryType));
comm.Parameters.Add(SqlHelper.GetParameter("@PayType", model.PayType));
comm.Parameters.Add(SqlHelper.GetParameter("@CurrencyType", model.CurrencyType));
comm.Parameters.Add(SqlHelper.GetParameter("@OpenBank", model.OpenBank));
comm.Parameters.Add(SqlHelper.GetParameter("@AccountMan", model.AccountMan));
comm.Parameters.Add(SqlHelper.GetParameter("@AccountNum", model.AccountNum));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", model.UsedStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate == null ? SqlDateTime.Null : SqlDateTime.Parse(model.CreateDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@AllowDefaultDays", model.AllowDefaultDays));
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int));
comm.CommandText = sqlArrive.ToString();
listADD.Add(comm);
#endregion
try
{
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
ID = comm.Parameters["@ID"].Value.ToString();
result = true;
}
return result;
}
catch (Exception ex)
{
throw ex;
}
}
