protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
ProductPriceChangeModel model = new ProductPriceChangeModel();
model.ChangeNo = txt_ChangeNo.Value;
model.Title = txt_Title.Value;
model.ProductID = hf_ProductID.Value;
model.Chenger = txtChenger.Value;
string OpenDate = txtOpenDate.Value;
string CloseDate = txtCloseDate.Value;
int totalCount = 0;
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
DataTable dt = ProductPriceChangeBus.GetProductPriceInfo(model, OpenDate, CloseDate, 1, 1000000, "ID desc",ref totalCount);
foreach (DataRow row in dt.Rows)
{
if (row["BillStatus"].ToString() == "1")
row["BillStatus"] = "制单";
if (row["BillStatus"].ToString() == "5")
row["BillStatus"] = "结单";
}
//导出标题
string headerTitle = "变更单编号|变更单主题|物品编号|物品名称|调整后销项率|调整后去税价|调整后含税价|调整后折扣|申请日期|确认日期|单据状态";
//string headerTitle = "建档日期|启用状态";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "ChangeNo|Title|ProductID|ProductName|TaxRateNew|StandardSellNew|SellTaxNew|DiscountNew|ChangeDate|ConfirmDate|BillStatus";
//string columnFiled = "CreateDate|strUsedStatus";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, " 物品售价变更单");
}
/// <summary>
/// 查询获取物品信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public static DataTable GetProductPriceInfo(ProductPriceChangeModel Model, string Starttime, string Endtime, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return ProductPriceChangeDBHelper.GetProductPriceInfo(Model, Starttime, Endtime, pageIndex, pageCount, OrderBy, ref totalCount);
}
catch (Exception ex)
{
return null;
throw ex;
}
}
/// <summary>
/// 修改物品档案
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpdateProductPriceInfo(ProductPriceChangeModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
bool succ = false;
LogInfoModel logModel = InitLogInfo(model.ChangeNo);
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
succ = ProductPriceChangeDBHelper.UpdateProductPriceInfo(model);
if (!succ)
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
else
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
LogDBHelper.InsertLog(logModel);
return succ;
}
catch (Exception ex)
{
WriteSystemLog(userInfo, ex);
return false;
}
}
/// <summary>
/// 修改物品档案
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool UpdateProductPriceInfo(ProductPriceChangeModel model)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE officedba.ProductPriceChange ");
sql.AppendLine(" SET ");
sql.AppendLine(" Title = @Title ");
sql.AppendLine(" ,ProductID = @ProductID ");
sql.AppendLine(" ,StandardSell = @StandardSell ");
sql.AppendLine(" ,SellTax = @SellTax ");
sql.AppendLine(" ,StandardSellNew = @StandardSellNew ");
sql.AppendLine(" ,SellTaxNew = @SellTaxNew ");
sql.AppendLine(" ,ChangeDate = @ChangeDate ");
sql.AppendLine(" ,Chenger = @Chenger ");
sql.AppendLine(" ,Remark = @Remark ");
sql.AppendLine(" ,BillStatus = @BillStatus ");
sql.AppendLine(" ,Creator = @Creator ");
sql.AppendLine(" ,CreateDate = @CreateDate ");
sql.AppendLine(" ,Confirmor = @Confirmor ");
sql.AppendLine(" ,ModifiedDate = @ModifiedDate ");
sql.AppendLine(" ,TaxRate = @TaxRate");
sql.AppendLine(" ,TaxRateNew = @TaxRateNew");
sql.AppendLine(" ,Discount = @Discount");
sql.AppendLine(" ,DiscountNew = @DiscountNew");
sql.AppendLine(" ,ModifiedUserID = @ModifiedUserID");
sql.AppendLine(" WHERE ID=@ID and CompanyCD=@CompanyCD ");
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
SetSaveParameter(comm, model,true);//其他参数
//执行更新并设置更新结果
bool result = false;
result = SqlHelper.ExecuteTransWithCommand(comm);
return result;
}
/// <summary>
/// 设置参数
/// </summary>
/// <param name="comm"></param>
/// <param name="model"></param>
private static void SetSaveParameter(SqlCommand comm, ProductPriceChangeModel model,bool flag)
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//公司代码
if (!flag)
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ChangeNo", model.ChangeNo));
}
else
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", Convert.ToString(model.ID)));
}
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", model.ProductID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardSell", model.StandardSell));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@SellTax", model.SellTax));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardSellNew", model.StandardSellNew));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@SellTaxNew", model.SellTaxNew));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ChangeDate", model.ChangeDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Chenger", model.Chenger));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaxRate", model.TaxRate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Discount", model.Discount));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaxRateNew", model.TaxRateNew));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DiscountNew", model.DiscountNew));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", model.ModifiedDate));//最后更新日期
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));//最后更新用户ID(对应操作用户U serID)
}
/// <summary>
/// 查询物品信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public static DataTable GetProductPriceInfo(ProductPriceChangeModel model, string starttime, string endtime, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("SELECT a.ID ");
searchSql.AppendLine(" ,a.ChangeNo ");
searchSql.AppendLine(" ,a.CompanyCD ");
searchSql.AppendLine(" ,a.Title ");
searchSql.AppendLine(" ,isnull(b.ProdNo,'')as ProductID ");
searchSql.AppendLine(" ,isnull(Convert(numeric(14,"+userInfo.SelPoint+"),a.StandardSell),0)as StandardSell");
searchSql.AppendLine(" ,isnull(Convert(numeric(14,"+userInfo.SelPoint+"),a.SellTax),0)as SellTax ");
searchSql.AppendLine(" ,isnull(Convert(numeric(14,"+userInfo.SelPoint+"),a.StandardSellNew),0)as StandardSellNew ");
searchSql.AppendLine(" ,isnull(Convert(numeric(14,"+userInfo.SelPoint+"),a.TaxRateNew),0)as TaxRateNew ");
searchSql.AppendLine(" ,isnull(Convert(numeric(12," + userInfo.SelPoint + "),a.DiscountNew),0)as DiscountNew ");
searchSql.AppendLine(" ,isnull(Convert(numeric(12,"+userInfo.SelPoint+"),a.Discount),0)as Discount ");
searchSql.AppendLine(" ,isnull(Convert(numeric(14,"+userInfo.SelPoint+"),a.TaxRate),0)as TaxRate ");
searchSql.AppendLine(" ,isnull(Convert(numeric(14,"+userInfo.SelPoint+"),a.SellTaxNew),0) as SellTaxNew ");
searchSql.AppendLine(" ,isnull( CONVERT(CHAR(19), a.ChangeDate, 120),'') as ChangeDate ");
searchSql.AppendLine(" ,isnull(a.Chenger,'')as Chenger ");
searchSql.AppendLine(" ,isnull(a.Remark,'')as Remark ");
searchSql.AppendLine(" ,isnull(a.BillStatus,'') as BillStatus ");
searchSql.AppendLine(" ,isnull(a.Creator,'')as Creator ");
searchSql.AppendLine(" ,isnull( CONVERT(CHAR(19), a.CreateDate, 120),'') as CreateDate ");
searchSql.AppendLine(" ,isnull(a.Confirmor,'')as Confirmor ");
searchSql.AppendLine(" ,isnull( CONVERT(CHAR(19), a.ConfirmDate, 120),'') as ConfirmDate ");
searchSql.AppendLine(" ,isnull(b.ProductName,'')as ProductName ");
searchSql.AppendLine(" ,isnull(c.EmployeeName,'') as ConfirmName");
searchSql.AppendLine(" FROM officedba.ProductPriceChange as a ");
searchSql.AppendLine("left join officedba.ProductInfo as b on a.ProductID=b.ID and a.CompanyCD=b.CompanyCD ");
searchSql.AppendLine("left join officedba.EmployeeInfo as c on a.Confirmor=c.ID and a.CompanyCD=c.CompanyCD ");
searchSql.AppendLine(" where a.CompanyCD=@CompanyCD ");
//#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
////公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//编号
if (!string.IsNullOrEmpty(model.ChangeNo))
{
searchSql.AppendLine(" and a.ChangeNo LIKE @ChangeNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ChangeNo", "%" + model.ChangeNo + "%"));
}
//名称
if (!string.IsNullOrEmpty(model.Title))
{
searchSql.AppendLine(" AND a.Title LIKE @Title ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", "%" + model.Title + "%"));
}
if (!string.IsNullOrEmpty(model.ProductID))
{
string ID = model.ProductID;
string allID = "";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string[] IdS = null;
ID = ID.Substring(0, ID.Length);
IdS = ID.Split(',');
for (int i = 0; i < IdS.Length; i++)
{
IdS[i] = "'" + IdS[i] + "'";
sb.Append(IdS[i]);
}
allID = sb.ToString().Replace("''", "','");
searchSql.AppendLine(" AND a.ProductID in (" + allID + ") ");
}
if (!string.IsNullOrEmpty(model.Chenger))
{
string ID = model.Chenger;
string allID = "";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string[] IdS = null;
ID = ID.Substring(0, ID.Length);
IdS = ID.Split(',');
for (int i = 0; i < IdS.Length; i++)
{
IdS[i] = "'" + IdS[i] + "'";
sb.Append(IdS[i]);
}
allID = sb.ToString().Replace("''", "','");
searchSql.AppendLine(" AND a.Chenger in (" + allID + ") ");
}
//时间
if (!string.IsNullOrEmpty(starttime))
{
searchSql.AppendLine(" AND a.ChangeDate >= @starttime ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@starttime", starttime));
}
if (!string.IsNullOrEmpty(endtime))
{
searchSql.AppendLine(" AND a.ChangeDate<=@endtime");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@endtime", endtime));
}
comm.CommandText = searchSql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount);
}
/// <summary>
/// 插入物品档案信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertProductPriceInfo(ProductPriceChangeModel model, out string ID)
{
//SQL拼写
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.ProductPriceChange");
sql.AppendLine(" (ChangeNo ");
sql.AppendLine(" ,CompanyCD ");
sql.AppendLine(" ,Title ");
sql.AppendLine(" ,ProductID ");
sql.AppendLine(" ,StandardSell ");
sql.AppendLine(" ,SellTax ");
sql.AppendLine(" ,StandardSellNew ");
sql.AppendLine(" ,SellTaxNew ");
sql.AppendLine(" ,ChangeDate ");
sql.AppendLine(" ,Chenger ");
sql.AppendLine(" ,Remark ");
sql.AppendLine(" ,BillStatus ");
sql.AppendLine(" ,Creator ");
sql.AppendLine(" ,CreateDate ");
sql.AppendLine(" ,Confirmor ");
sql.AppendLine(" ,ModifiedDate ");
sql.AppendLine(" ,TaxRate ");
sql.AppendLine(" ,Discount ");
sql.AppendLine(" ,TaxRateNew ");
sql.AppendLine(" ,DiscountNew ");
sql.AppendLine(" ,ModifiedUserID) ");
sql.AppendLine(" VALUES ");
sql.AppendLine(" (@ChangeNo ");
sql.AppendLine(" ,@CompanyCD ");
sql.AppendLine(" ,@Title ");
sql.AppendLine(" ,@ProductID ");
sql.AppendLine(" ,@StandardSell ");
sql.AppendLine(" ,@SellTax ");
sql.AppendLine(" ,@StandardSellNew ");
sql.AppendLine(" ,@SellTaxNew ");
sql.AppendLine(" ,@ChangeDate ");
sql.AppendLine(" ,@Chenger ");
sql.AppendLine(" ,@Remark ");
sql.AppendLine(" ,@BillStatus ");
sql.AppendLine(" ,@Creator ");
sql.AppendLine(" ,@CreateDate ");
sql.AppendLine(" ,@Confirmor ");
sql.AppendLine(" ,@ModifiedDate ");
sql.AppendLine(" ,@TaxRate ");
sql.AppendLine(" ,@Discount ");
sql.AppendLine(" ,@TaxRateNew ");
sql.AppendLine(" ,@DiscountNew ");
sql.AppendLine(" ,@ModifiedUserID ) ");
sql.AppendLine(" SET @ID= @@IDENTITY ");
//定义更新基本信息的命令
SqlCommand comm = new SqlCommand();
//设置存储过程名
comm.CommandText = sql.ToString();
//设置保存的参数
SetSaveParameter(comm, model,false);
//添加返回参数
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int));
//执行登陆操作
bool isSucc = SqlHelper.ExecuteTransWithCommand(comm);
//设置ID
//model.ID = int.Parse(comm.Parameters["@ProdID"].Value);
ID = comm.Parameters["@ID"].Value.ToString();
return isSucc;
}
