public static DataTable GetStorageInitailTableBycondition(string BatchNo,StorageInitailModel model, string timeStart, string timeEnd, string orderby)
{
try
{
return StorageInitailInfoDBHelper.GetStorageInitailTableBycondition(BatchNo,model, timeStart, timeEnd, orderby);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取期初库存和期初库存详细信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetStorageInitailDetailInfo(StorageInitailModel model)
{
try
{
return StorageInitailInfoDBHelper.GetStorageInitailDetailInfo(model);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 查询期初库存列表
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetStorageInitailTableBycondition(string BatchNo,string EFIndex,string EFDesc,StorageInitailModel model, string timeStart, string timeEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
return StorageInitailInfoDBHelper.GetStorageInitailTableBycondition(BatchNo,EFIndex, EFDesc, model, timeStart, timeEnd, pageIndex, pageCount, ord, ref TotalCount);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 期初库存插入（插入期初库存和期初库存详细）
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertStorageInitail(StorageInitailModel model, List<StorageInitailDetailModel> modelList,Hashtable ht, out int IndexIDentity)
{
IndexIDentity = 0;
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//定义返回变量
bool isSucc = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//获取公司代码
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
//执行删除操作
try
{
//执行更新
isSucc = StorageInitailInfoDBHelper.InsertStorageInitail(model, modelList,ht, out IndexIDentity);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (isSucc)
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
//操作日志
LogInfoModel logModel = InitLogInfo(model.InNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return isSucc;
}
/// <summary>
/// 保存时基本信息参数设置
/// </summary>
/// <param name="comm"></param>
/// <param name="model"></param>
private static void SetSaveParameter(SqlCommand comm, StorageInitailModel model)
{
if (model.ID != null && model.ID != "")
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID ", model.ID));//自动生成
}
else
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator ", model.Creator));//制单人
}
//@CompanyCD,@InNo,@Title,@StorageID,@Executor,@EnterDate,
//@Remark,@Creator,@CreateDate,@BillStatus,getdate(),@ModifiedUserID,@DeptID,@Summary,@TotalPrice,@CountTotal
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD ", model.CompanyCD));//公司编码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@InNo ", model.InNo));//入库单编号
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title ", model.Title));//主题
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID ", model.StorageID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Executor ", model.Executor));//
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EnterDate ", model.EnterDate));//
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark ", model.Remark));//
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate ", model.CreateDate));//
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus ", model.BillStatus));//
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID ", model.ModifiedUserID));//备注
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID ", model.DeptID));//
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Summary ", model.Summary));//
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalPrice ", model.TotalPrice));//
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CountTotal ", model.CountTotal));//
}
public static bool CancelCloseBill(StorageInitailModel model)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageInitail SET");
sql.AppendLine(" Closer = NULL,");
sql.AppendLine(" CloseDate = NULL,");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = getdate()");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID));
return SqlHelper.ExecuteTransWithCommand(comm);
}
public static bool ConfirmBill(StorageInitailModel model)
{
ArrayList lstConfirm = new ArrayList();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageInitail SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" confirmDate = getdate(),");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = getdate()");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID));
lstConfirm.Add(comm);
List<StorageInitailDetailModel> modelList = new List<StorageInitailDetailModel>();
string sqlSele = "select a.ID,a.CompanyCD, a.ProductID,b.StorageID,a.BatchNo,a.InNo BillNo,a.UnitPrice Price,Convert(varchar(10),b.EnterDate,23) HappenDate," +
" a.Remark, a.ProductCount from officedba.StorageInitailDetail a left join" +
" officedba.StorageInitail b on a.InNo=b.InNo and a.CompanyCD = b.CompanyCD where a.CompanyCD='" + model.CompanyCD + "' and a.InNo=(select InNo from officedba.StorageInitail where ID=" + model.ID + ")";
DataTable dt = SqlHelper.ExecuteSql(sqlSele);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StorageInitailDetailModel modelDetail = new StorageInitailDetailModel();
StorageAccountModel StorageAccountM = new StorageAccountModel();
if (dt.Rows[i]["ProductID"].ToString() != "")
{
modelDetail.ProductID = dt.Rows[i]["ProductID"].ToString();
StorageAccountM.ProductID = Convert.ToInt32(dt.Rows[i]["ProductID"].ToString());
}
if (dt.Rows[i]["ProductCount"].ToString() != "")
{
modelDetail.ProductCount = dt.Rows[i]["ProductCount"].ToString();
StorageAccountM.HappenCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
StorageAccountM.ProductCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
}
if (dt.Rows[i]["StorageID"].ToString() != "")
{
model.StorageID = dt.Rows[i]["StorageID"].ToString();
StorageAccountM.StorageID = Convert.ToInt32(dt.Rows[i]["StorageID"].ToString());
}
//插入流水账表
StorageAccountM.CompanyCD = dt.Rows[i]["CompanyCD"].ToString();
StorageAccountM.BillType = 1;
if (dt.Rows[i]["BatchNo"].ToString() != "")
{
modelDetail.BatchNo = dt.Rows[i]["BatchNo"].ToString();
StorageAccountM.BatchNo = dt.Rows[i]["BatchNo"].ToString();
}
modelList.Add(modelDetail);
StorageAccountM.BillNo = dt.Rows[i]["BillNo"].ToString();
StorageAccountM.Price = Convert.ToDecimal(dt.Rows[i]["Price"].ToString());
StorageAccountM.HappenDate = Convert.ToDateTime(dt.Rows[i]["HappenDate"].ToString());
StorageAccountM.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
StorageAccountM.PageUrl = "../Office/StorageManager/StorageInitailAdd.aspx";
StorageAccountM.ReMark = dt.Rows[i]["Remark"].ToString();
SqlCommand commSA = new SqlCommand();
commSA = StorageAccountDBHelper.InsertStorageAccountCommand(StorageAccountM, "0");
lstConfirm.Add(commSA);
}
}
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commPD = new SqlCommand();
if (Exists(modelList[i].BatchNo,model.StorageID, modelList[i].ProductID, model.CompanyCD))
{
commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, model.StorageID, modelList[i].ProductCount, model, true);
}
else
{
commPD = InsertStorageProduct(modelList[i].BatchNo,modelList[i].ProductID, model.StorageID, modelList[i].ProductCount, model.CompanyCD);
}
lstConfirm.Add(commPD);
}
}
return SqlHelper.ExecuteTransWithArrayList(lstConfirm);
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(StorageInitailModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.StorageInitail set ";
foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例
{
strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ",";
cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim());
}
int iLength = strSql.Length - 1;
strSql = strSql.Substring(0, iLength);
strSql += " where CompanyCD = @CompanyCD AND InNo = @InNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@InNo", model.InNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}
public static bool UpdateStorageInitail(StorageInitailModel model, List<StorageInitailDetailModel> modelList,Hashtable ht)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update officedba.StorageInitail set ");
strSql.Append("Title=@Title,");
strSql.Append("StorageID=@StorageID,");
strSql.Append("Executor=@Executor,");
strSql.Append("EnterDate=@EnterDate,");
strSql.Append("Remark=@Remark,");
strSql.Append("BillStatus=@BillStatus,");
strSql.Append("ModifiedDate=getdate(),");
strSql.Append("ModifiedUserID=@ModifiedUserID,");
strSql.Append("DeptID=@DeptID,");
strSql.Append("Summary=@Summary,");
strSql.Append("TotalPrice=@TotalPrice,");
strSql.Append("CountTotal=@CountTotal");
strSql.Append(" where ID=@ID ");
SqlCommand comm = new SqlCommand(strSql.ToString());
SetSaveParameter(comm, model);
ArrayList lstUpdate = new ArrayList();
lstUpdate.Add(comm);//数组加入插入基表的command
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, ht, cmd);
if (ht.Count > 0)
lstUpdate.Add(cmd);
#endregion
//先删掉明细表中对应单据的所有数据
string delDetail = "delete from officedba.StorageInitailDetail where CompanyCD='" + model.CompanyCD + "' and InNo='" + model.InNo + "'";
SqlCommand commdel = new SqlCommand(delDetail);
lstUpdate.Add(commdel);
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
StringBuilder strSqlDetail = new StringBuilder();
strSqlDetail.Append("insert into officedba.StorageInitailDetail(");
strSqlDetail.Append("InNo,SortNo,ProductID,UnitPrice,ProductCount,TotalPrice,Remark,ModifiedDate,ModifiedUserID,CompanyCD,UsedUnitID,UsedUnitCount,UsedPrice,ExRate,BatchNo)");
strSqlDetail.Append(" values (");
strSqlDetail.Append("@InNo,@SortNo,@ProductID,@UnitPrice,@ProductCount,@TotalPrice,@Remark,getdate(),@ModifiedUserID,@CompanyCD,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate,@BatchNo)");
strSqlDetail.Append(";select @@IDENTITY");
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commDetail = new SqlCommand();
commDetail.CommandText = strSqlDetail.ToString();
EditInitailDetailInfo(commDetail, modelList[i]);
lstUpdate.Add(commDetail);//循环加入数组（重新获取页面上明细数据）
}
}
return SqlHelper.ExecuteTransWithArrayList(lstUpdate);
}
/// <summary>
/// 添加期初入库单信息及其详细信息
/// </summary>
/// <returns>DataTable</returns>
public static bool InsertStorageInitail(StorageInitailModel model, List<StorageInitailDetailModel> modelList,Hashtable ht, out int IndexIDentity)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("insert into officedba.StorageInitail(");
strSql.AppendLine("CompanyCD,InNo,Title,StorageID,Executor,EnterDate,Remark,Creator,CreateDate,BillStatus,ModifiedDate,ModifiedUserID,DeptID,Summary,TotalPrice,CountTotal)");
strSql.AppendLine(" values (");
strSql.AppendLine("@CompanyCD,@InNo,@Title,@StorageID,@Executor,@EnterDate,@Remark,@Creator,getdate(),@BillStatus,getdate(),@ModifiedUserID,@DeptID,@Summary,@TotalPrice,@CountTotal)");
strSql.AppendLine("set @IndexID = @@IDENTITY");
SqlCommand comm = new SqlCommand();
comm.CommandText = strSql.ToString();
SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int);
IndexID.Direction = ParameterDirection.Output;
comm.Parameters.Add(IndexID);
SetSaveParameter(comm, model);
ArrayList lstInsert = new ArrayList();
lstInsert.Add(comm);//数组加入插入基表的command
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, ht, cmd);
if (ht.Count > 0)
lstInsert.Add(cmd);
#endregion
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
//插入期初入库明细
StringBuilder strSqlDetail = new StringBuilder();
strSqlDetail.Append("insert into officedba.StorageInitailDetail(");
strSqlDetail.Append("InNo,SortNo,ProductID,UnitPrice,ProductCount,TotalPrice,Remark,ModifiedDate,ModifiedUserID,CompanyCD,UsedUnitID,UsedUnitCount,UsedPrice,ExRate,BatchNo)");
strSqlDetail.Append(" values (");
strSqlDetail.Append("@InNo,@SortNo,@ProductID,@UnitPrice,@ProductCount,@TotalPrice,@Remark,getdate(),@ModifiedUserID,@CompanyCD,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate,@BatchNo)");
strSqlDetail.Append(";select @@IDENTITY");
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commDetail = new SqlCommand();
commDetail.CommandText = strSqlDetail.ToString();
EditInitailDetailInfo(commDetail, modelList[i]);
lstInsert.Add(commDetail);//循环加入数组（重新获取页面上明细数据）
}
}
bool result = SqlHelper.ExecuteTransWithArrayList(lstInsert);
if (result)
{
IndexIDentity = int.Parse(((SqlCommand)lstInsert[0]).Parameters["@IndexID"].Value.ToString());
}
else
{
IndexIDentity = 0;
}
return result;
}
public static DataTable GetStorageInitailDetailInfo(StorageInitailModel model)
{
//string[] sql = new string[2];
//查询期初入库详细信息
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT a.ID ");
sql.AppendLine(",a.InNo, b.BatchNo ");
sql.AppendLine(",a.Title ");
sql.AppendLine(",a.StorageID ");
sql.AppendLine(",s.StorageName ");
sql.AppendLine(",a.Executor ");
sql.AppendLine(",a.ExtField1 ");
sql.AppendLine(",a.ExtField2 ");
sql.AppendLine(",a.ExtField3 ");
sql.AppendLine(",a.ExtField4 ");
sql.AppendLine(",a.ExtField5 ");
sql.AppendLine(",a.ExtField6 ");
sql.AppendLine(",a.ExtField7 ");
sql.AppendLine(",a.ExtField8 ");
sql.AppendLine(",a.ExtField9 ");
sql.AppendLine(",a.ExtField10 ");
sql.AppendLine(",ISNULL(f.EmployeeName,'') as ExecutorName ");
sql.AppendLine(",case when a.EnterDate Is NULL then '' else CONVERT(VARCHAR(10),a.EnterDate, 21) end AS EnterDate ");
sql.AppendLine(",ISNULL(a.Remark,'') as Remark ");
sql.AppendLine(",a.Creator ");
sql.AppendLine(",ISNULL(g.EmployeeName,'') as CreatorName ");
sql.AppendLine(",case when a.CreateDate Is NULL then '' else CONVERT(VARCHAR(10),a.CreateDate, 21) end AS CreateDate ");
sql.AppendLine(",a.Confirmor ");
sql.AppendLine(",ISNULL(h.EmployeeName,'') as ConfirmorName ");
sql.AppendLine(",case when a.ConfirmDate Is NULL then '' else CONVERT(VARCHAR(10),a.ConfirmDate, 21) end AS ConfirmDate ");
sql.AppendLine(",a.Closer ");
sql.AppendLine(",ISNULL(i.EmployeeName,'') as CloserName ");
sql.AppendLine(",case when a.CloseDate Is NULL then '' else CONVERT(VARCHAR(10),a.CloseDate, 21) end AS CloseDate ");
sql.AppendLine(",case when a.ModifiedDate Is NULL then '' else CONVERT(VARCHAR(10),a.ModifiedDate, 21) end AS ModifiedDate ");
sql.AppendLine(",a.ModifiedUserID as ModifiedUserName ");
sql.AppendLine(",a.BillStatus ");
sql.AppendLine(",case a.BillStatus when '1' then '制单' when '2' then '执行' when '3' then '变更'");
sql.AppendLine("when '4' then '手工结单' when '5' then '自动结单' else '' end as BillStatusName ");
sql.AppendLine(",a.DeptID ");
sql.AppendLine(",ISNULL(j.DeptName,'') as DeptName ");
sql.AppendLine(",ISNULL(a.Summary,'') as Summary ");
sql.AppendLine(",ISNULL(a.TotalPrice,'0') as A_TotalPrice ");
sql.AppendLine(",ISNULL(a.CountTotal,'0') as CountTotal ");
sql.AppendLine(",b.ID as DetailID ");
sql.AppendLine(",b.ProductID ");
sql.AppendLine(",b.SortNo ");
sql.AppendLine(",ISNULL(c.ProdNo,'') as ProductNo ");
sql.AppendLine(",ISNULL(c.ProductName,'') as ProductName ");
sql.AppendLine(",ISNULL(q.CodeName,'') as UnitID ");
sql.AppendLine(",ISNULL(c.Specification,'') as Specification ");
sql.AppendLine(",ISNULL(b.UnitPrice,0) as UnitPrice ");
sql.AppendLine(",ISNULL(b.TotalPrice,'0') as B_TotalPrice ");
sql.AppendLine(",ISNULL(b.ProductCount,0) as ProductCount ");
sql.AppendLine(",ISNULL(b.Remark,'') as DetaiRemark,b.TotalPrice");
sql.AppendLine(",b.UsedUnitID as UsedUnitID,z.CodeName UsedUnitName ");
sql.AppendLine(", b.UsedUnitCount as UsedUnitCount ");
sql.AppendLine(",ISNULL(b.UsedPrice,0) as UsedPrice ");
sql.AppendLine(",b.ExRate as ExRate ");
sql.AppendLine(",c.IsBatchNo ");
sql.AppendLine("FROM officedba.StorageInitail a ");
sql.AppendLine("left join officedba.StorageInitailDetail b on a.InNo=b.InNo and a.CompanyCD=b.CompanyCD ");
sql.AppendLine("left join officedba.ProductInfo c on c.ID=b.ProductID ");
sql.AppendLine("left join officedba.EmployeeInfo f on a.Executor=f.ID ");
sql.AppendLine("left join officedba.EmployeeInfo g on a.Creator=g.ID ");
sql.AppendLine("left join officedba.EmployeeInfo h on a.Confirmor=h.ID ");
sql.AppendLine("left join officedba.EmployeeInfo i on a.Closer=i.ID ");
sql.AppendLine("left join officedba.DeptInfo j on a.DeptID=j.ID ");
sql.AppendLine("left join officedba.CodeUnitType q on q.ID=c.UnitID ");
sql.AppendLine("left join officedba.StorageInfo s on s.ID=a.StorageID ");
sql.AppendLine("left join officedba.CodeUnitType z on z.ID=b.UsedUnitID ");
sql.AppendLine("left join (select w.UserID,x.EmployeeName from officedba.UserInfo w ,officedba.EmployeeInfo x where w.EmployeeID =x.ID) m ");
sql.AppendLine(" on a.ModifiedUserID=m.UserID ");
sql.AppendLine(" where a.CompanyCD='" + model.CompanyCD + "' and a.id=" + model.ID);
return SqlHelper.ExecuteSql(sql.ToString());
//SortNo,InNo,ProductID,UnitID,UnitPrice,TotalPrice,Remark
//查询标准工序下的工艺明细列表信息
// sql[1] = "select * from officedba.StorageInitailDetail a inner join officedba.StorageInitail b on b.InNo=a.InNo and b.CompanyCD='" + model.CompanyCD;
//return SqlHelper.ExecuteSql(sql.ToString());
}
protected void LoadPrintInfo()
{
PrintParameterSettingModel model = new PrintParameterSettingModel();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_STORAGE);
model.PrintTypeFlag = ConstUtil.PRINTBILL_TYPEFLAG_Init;
StorageInitailModel OutSellM_ = new StorageInitailModel();
OutSellM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
OutSellM_.ID = this.intMrpID.ToString();
string[,] aDetail;
/*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/
string[,] aBase = {
{ "{ExtField1}", "ExtField1"},
{ "{ExtField2}", "ExtField2"},
{ "{ExtField3}", "ExtField3"},
{ "{ExtField4}", "ExtField4"},
{ "{ExtField5}", "ExtField5"},
{ "{ExtField6}", "ExtField6"},
{ "{ExtField7}", "ExtField7"},
{ "{ExtField8}", "ExtField8"},
{ "{ExtField9}", "ExtField9"},
{ "{ExtField10}", "ExtField10"},
{ "入库单编号", "InNo"},
{ "入库单主题 ", "Title"},
{ "仓库", "StorageName" },
{ "入库部门 ", "DeptName" },
{ "入库人", "ExecutorName"},
{ "入库时间", "EnterDate"},
{ "摘要 ", "Summary"},
{ "批次", "BatchNo"},
{ "入库数量合计 ", "CountTotal"},
{ "入库金额合计 ", "A_TotalPrice"},
{ "制单人", "CreatorName"},
{ "制单日期", "CreateDate"},
{ "单据状态", "BillStatusName"},
{ "确认人", "ConfirmorName"},
{ "确认日期", "ConfirmDate"},
{ "结单人", "CloserName"},
{ "结单日期", "CloseDate"},
{ "最后更新人", "ModifiedUserName"},
{ "最后更新日期", "ModifiedDate"},
{ "备注", "Remark"},
};
if (HiddenMoreUnit.Value == "true")
{
aDetail = new string[,] {
{ "序号", "SortNo"},
{ "物品编号", "ProductNo"},
{ "物品名称", "ProductName" },
{ "规格", "Specification" },
{ "基本单位", "UnitID"},
{ "基本数量", "ProductCount"},
{ "单位", "UsedUnitName"}, //++
{ "数量", "UsedUnitCount"}, //++
{ "入库单价 ", "UnitPrice"},
{ "入库金额 ", "B_TotalPrice"},
{ "备注", "DetaiRemark"},
};
}
else
{
aDetail = new string[,] {
{ "序号", "SortNo"},
{ "物品编号", "ProductNo"},
{ "物品名称", "ProductName" },
{ "规格", "Specification" },
{ "单位", "UnitID"},
{ "数量", "ProductCount"},
{ "入库单价 ", "UnitPrice"},
{ "入库金额 ", "B_TotalPrice"},
{ "备注", "DetaiRemark"},
};
}
#region 1.扩展属性
DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageInitail");
if (dtExtTable.Rows.Count > 0)
{
for (int i = 0; i < dtExtTable.Rows.Count; i++)
{
for (int x = 0; x < (aBase.Length / 2) - 15; x++)
{
if (x == i)
{
aBase[x, 0] = dtExtTable.Rows[i]["EFDesc"].ToString();
}
}
}
}
#endregion
DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model);
DataTable dtMain = StorageInitailBus.GetStorageInitailDetailInfo(OutSellM_);
// DataTable dtDetail = MRPBus.GetMRPDetailInfo(modelMRP);
string strBaseFields = "";
string strDetailFields = "";
if (dbPrint.Rows.Count > 0)
{
isSeted.Value = "1";
strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString();
strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString();
}
else
{
isSeted.Value = "0";
strBaseFields = "InNo|Title|StorageName|DeptName|ExecutorName|EnterDate|Summary|BatchNo|CountTotal|A_TotalPrice"
+ "|CreatorName|CreateDate|BillStatusName|ConfirmorName|ConfirmDate|CloserName|CloseDate"
+ "|ModifiedUserName|ModifiedDate|Remark"
+ "|ExtField1|ExtField2|ExtField3|ExtField4|ExtField5|ExtField6|ExtField7|ExtField8|ExtField9|ExtField10";
if (HiddenMoreUnit.Value == "True")
{
strDetailFields = "SortNo|ProductNo|ProductName|Specification|UnitID|ProductCount|UsedUnitName|UsedUnitCount|UnitPrice|B_TotalPrice|DetaiRemark";
}
else
{
strDetailFields = "SortNo|ProductNo|ProductName|Specification|UnitID|ProductCount|UnitPrice|B_TotalPrice|DetaiRemark";
}
}
#region 主表信息
if (!string.IsNullOrEmpty(strBaseFields))
{
tableBase.InnerHtml = WritePrintPageTable("期初库存录入单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMain, dtMain, true);
}
#endregion
#region 明细信息
if (!string.IsNullOrEmpty(strDetailFields))
{
tableDetail.InnerHtml = WritePrintPageTable("期初库存录入单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMain, dtMain, false);
}
#endregion
}
protected void LoadPrintInfo()
{
PrintParameterSettingModel model = new PrintParameterSettingModel();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_STORAGE);
model.PrintTypeFlag = ConstUtil.PRINTBILL_TYPEFLAG_BORROW;
StorageInitailModel OutSellM_ = new StorageInitailModel();
OutSellM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
OutSellM_.ID = this.intMrpID.ToString();
/*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/
string[,] aBase = {
{ "{ExtField1}", "ExtField1"},
{ "{ExtField2}", "ExtField2"},
{ "{ExtField3}", "ExtField3"},
{ "{ExtField4}", "ExtField4"},
{ "{ExtField5}", "ExtField5"},
{ "{ExtField6}", "ExtField6"},
{ "{ExtField7}", "ExtField7"},
{ "{ExtField8}", "ExtField8"},
{ "{ExtField9}", "ExtField9"},
{ "{ExtField10}", "ExtField10"},
{ "借货单编号", "BorrowNo"},
{ "借货单主题 ", "Title"},
{ "借货人", "BorrowerText" },
{ "借货部门", "DeptName" },
{ "借货原因", "Reason"},
{ "借货日期", "BorrowDate"},
{ "借出仓库 ", "SotorageName"},
{ "出库日期 ", "OutDate"},
{ "出库人 ", "OuterText"},
{ "借出部门", "OutDeptName"},
{ "摘要", "Summary"},
{ "借货数量合计 ", "CountTotal"},
{ "借货金额合计 ", "TotalPrice"},
{ "制单人", "CreatorText"},
{ "制单日期", "CreateDate"},
{ "单据状态", "BillStatusText"},
{ "确认人", "ConfirmorText"},
{ "确认日期", "ConfirmDate"},
{ "结单人", "CloserText"},
{ "结单日期", "CloseDate"},
{ "最后更新人", "ModifiedUserID"},
{ "最后更新日期", "ModifiedDate"},
{ "备注", "Remark"},
};
string[,] aDetail = null;
if (!UserInfo.IsMoreUnit)
aDetail = new string[,]{
{ "序号", "SortNo"},
{ "物品编号", "ProdNo"},
{ "物品名称", "ProductName" },
{"批次","BatchNo"},
{ "规格", "Specification" },
{ "单位", "CodeName"},
{ "现有存量", "UseCount"},
{ "借出单价 ", "UnitPrice"},
{ "借货数量 ", "ProductCount"},
{ "借货金额 ", "TotalPrice"},
{ "预计返还日期 ", "ReturnDate"},
{ "预计返还数量 ", "ReturnCount"},
{ "已返还数量 ", "RealReturnCount"},
{ "备注", "Remark"},
};
else
aDetail = new string[,] {
{ "序号", "SortNo"},
{ "物品编号", "ProdNo"},
{ "物品名称", "ProductName" },
{"批次","BatchNo"},
{ "规格", "Specification" },
{ "基本单位", "CodeName"},
{"基本数量","ProductCount"},
{"单位","UsedUnitName"},
{ "现有存量", "UseCount"},
{ "借出单价 ", "UsedPrice"},
{ "借货数量 ", "UsedUnitCount"},
{ "借货金额 ", "TotalPrice"},
{ "预计返还日期 ", "ReturnDate"},
{ "预计返还数量 ", "ReturnCount"},
{ "已返还数量 ", "RealReturnCount"},
{ "备注", "Remark"},
};
#region 1.扩展属性
int countExt = 0;
DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageBorrow");
if (dtExtTable.Rows.Count > 0)
{
for (int i = 0; i < dtExtTable.Rows.Count; i++)
{
for (int x = 0; x < (aBase.Length / 2) - 15; x++)
{
if (x == i)
{
aBase[x, 0] = dtExtTable.Rows[i]["EFDesc"].ToString();
countExt++;
}
}
}
}
#endregion
string No = Request.QueryString["No"].ToString();
DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model);
DataTable dtMRP = StorageBorrowBus.GetStorageBorrowInfo(intMrpID);
DataTable dtDetail = StorageBorrowBus.GetStorageBorrowDetail(OutSellM_.CompanyCD, No);
string strBaseFields = "";
string strDetailFields = "";
if (dbPrint.Rows.Count > 0)
{
#region 设置过打印模板设置时 直接取出表里设置的值
isSeted.Value = "1";
strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString();
strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString();
#endregion
}
else
{
#region 未设置过打印模板设置 默认显示所有的
isSeted.Value = "0";
/*未设置过打印模板设置时，默认显示的字段 基本信息字段*/
for (int m = 10; m < aBase.Length / 2; m++)
{
strBaseFields = strBaseFields + aBase[m, 1] + "|";
}
/*未设置过打印模板设置时，默认显示的字段 基本信息字段+扩展信息字段*/
if (countExt > 0)
{
for (int i = 0; i < countExt; i++)
{
strBaseFields = strBaseFields + "ExtField" + (i + 1) + "|";
}
}
/*未设置过打印模板设置时，默认显示的字段 明细信息字段*/
for (int n = 0; n < aDetail.Length / 2; n++)
{
strDetailFields = strDetailFields + aDetail[n, 1] + "|";
}
#endregion
}
#region 2.主表信息
if (!string.IsNullOrEmpty(strBaseFields))
{
tableBase.InnerHtml = WritePrintPageTable("借货申请单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, true);
}
#endregion
#region 3.明细信息
if (!string.IsNullOrEmpty(strDetailFields))
{
tableDetail.InnerHtml = WritePrintPageTable("借货申请单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, false);
}
#endregion
}
protected void LoadPrintInfo()
{
PrintParameterSettingModel model = new PrintParameterSettingModel();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_STORAGE);
model.PrintTypeFlag = ConstUtil.PRINTBILL_TYPEFLAG_TRANSFER;
StorageInitailModel OutSellM_ = new StorageInitailModel();
OutSellM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
OutSellM_.ID = this.intMrpID.ToString();
/*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/
string[,] aBase = {
{ "{ExtField1}", "ExtField1"},
{ "{ExtField2}", "ExtField2"},
{ "{ExtField3}", "ExtField3"},
{ "{ExtField4}", "ExtField4"},
{ "{ExtField5}", "ExtField5"},
{ "{ExtField6}", "ExtField6"},
{ "{ExtField7}", "ExtField7"},
{ "{ExtField8}", "ExtField8"},
{ "{ExtField9}", "ExtField9"},
{ "{ExtField10}", "ExtField10"},
{ "调拨单编号", "TransferNo"},
{ "调拨单主题", "Title"},
{ "调拨申请人", "ApplyUserIDName" },
{ "要货部门", "ApplyDeptIDName" },
{ "调入仓库", "InStorageName"},
{ "要求到货日期", "RequireInDate"},
{ "调拨原因 ", "Reason"},
{ "调货部门 ", "OutDeptIDName"},
{ "调出仓库 ", "OutStorageName"},
{ "业务状态", "BusiStatusText"},
{ "摘要", "Summary"},
{ "调拨数量合计 ", "TransferCount"},
{ "调拨金额合计 ", "TransferPrice"},
{ "调拨费用 ", "TransferFeeSum"},
{ "制单人", "CreatorName"},
{ "制单日期", "CreateDate"},
{ "单据状态", "BillStatusText"},
{ "确认人", "ConfirmorName"},
{ "确认日期", "ConfirmDate"},
{ "结单人", "CloserName"},
{ "结单日期", "CloseDate"},
{ "最后更新人", "ModifiedUserID"},
{ "最后更新日期", "ModifiedDate"},
{ "备注", "Remark"},
};
string[,] aDetail =null;
if(!UserInfo.IsMoreUnit)
aDetail= new string[,] {
{ "序号", "SortNo"},
{ "物品编号", "ProdNo"},
{ "物品名称", "ProductName" },
{"批次","BatchNo"},
{ "规格", "Specification" },
{ "单位", "UnitName"},
{ "调拨单价", "TranPrice"},
{ "调拨数量 ", "TranCount"},
{ "调拨金额 ", "TranPriceTotal"},
{ "已出库数量 ", "OutCount"},
{ "已入库数量 ", "InCount"},
{ "备注 ", "Remark"},
};
else
aDetail = new string[,] {
{ "序号", "SortNo"},
{ "物品编号", "ProdNo"},
{ "物品名称", "ProductName" },
{"批次","BatchNo"},
{ "规格", "Specification" },
{ "基本单位", "UnitName"},
{"基本数量","TranCount"},
{ "单位", "UsedUnitName"},
{ "调拨单价", "TranPrice"},
{ "调拨数量 ", "UsedUnitCount"},
{ "调拨金额 ", "TranPriceTotal"},
{ "已出库数量 ", "OutCount"},
{ "已入库数量 ", "InCount"},
{ "备注 ", "Remark"},
};
#region 1.扩展属性
int countExt = 0;
DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageTransfer");
if (dtExtTable.Rows.Count > 0)
{
for (int i = 0; i < dtExtTable.Rows.Count; i++)
{
for (int x = 0; x < (aBase.Length / 2) - 15; x++)
{
if (x == i)
{
aBase[x, 0] = dtExtTable.Rows[i]["EFDesc"].ToString();
countExt++;
}
}
}
}
#endregion
string No = Request.QueryString["No"].ToString();
DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model);
XBase.Model.Office.StorageManager.StorageTransfer st = new XBase.Model.Office.StorageManager.StorageTransfer();
st.ID = intMrpID;
XBase.Model.Office.StorageManager.StorageTransferDetail std = new XBase.Model.Office.StorageManager.StorageTransferDetail();
std.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
std.TransferNo = No;
/*读取数据*/
DataTable dtMRP = XBase.Business.Office.StorageManager.StorageTransferBus.GetStorageTransferInfoPrint(st);
DataTable dtDetail = XBase.Business.Office.StorageManager.StorageTransferBus.GetStorageTransferDetailInfo(std);
string strBaseFields = "";
string strDetailFields = "";
if (dbPrint.Rows.Count > 0)
{
#region 设置过打印模板设置时 直接取出表里设置的值
isSeted.Value = "1";
strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString();
strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString();
#endregion
}
else
{
#region 未设置过打印模板设置 默认显示所有的
isSeted.Value = "0";
/*未设置过打印模板设置时，默认显示的字段 基本信息字段*/
for (int m = 10; m < aBase.Length / 2; m++)
{
strBaseFields = strBaseFields + aBase[m, 1] + "|";
}
/*未设置过打印模板设置时，默认显示的字段 基本信息字段+扩展信息字段*/
if (countExt > 0)
{
for (int i = 0; i < countExt; i++)
{
strBaseFields = strBaseFields + "ExtField" + (i + 1) + "|";
}
}
/*未设置过打印模板设置时，默认显示的字段 明细信息字段*/
for (int n = 0; n < aDetail.Length / 2; n++)
{
strDetailFields = strDetailFields + aDetail[n, 1] + "|";
}
#endregion
}
#region 2.主表信息
if (!string.IsNullOrEmpty(strBaseFields))
{
tableBase.InnerHtml = WritePrintPageTable("库存调拨单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, true);
}
#endregion
#region 3.明细信息
if (!string.IsNullOrEmpty(strDetailFields))
{
tableDetail.InnerHtml = WritePrintPageTable("库存调拨单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, false);
}
#endregion
}
protected void LoadPrintInfo()
{
PrintParameterSettingModel model = new PrintParameterSettingModel();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_STORAGE);
model.PrintTypeFlag = ConstUtil.PRINTBILL_TYPEFLAG_ADJUST;
StorageInitailModel OutSellM_ = new StorageInitailModel();
OutSellM_.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
OutSellM_.ID = this.intMrpID.ToString();
/*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/
string[,] aDetail;
string[,] aBase = {
{ "{ExtField1}", "ExtField1"},
{ "{ExtField2}", "ExtField2"},
{ "{ExtField3}", "ExtField3"},
{ "{ExtField4}", "ExtField4"},
{ "{ExtField5}", "ExtField5"},
{ "{ExtField6}", "ExtField6"},
{ "{ExtField7}", "ExtField7"},
{ "{ExtField8}", "ExtField8"},
{ "{ExtField9}", "ExtField9"},
{ "{ExtField10}", "ExtField10"},
{ "单据编号", "AdjustNo"},
{ "单据标题", "Title"},
{ "经办人", "Executor" },
{ "调整部门", "DeptID" },
{ "调整仓库", "StorageID"},
{ "调整原因", "ReasonType"},
{ "调整日期 ", "AdjustDate"},
{ "摘要", "Summary"},
{ "调整数量合计 ", "CountTotal"},
{ "调整金额合计 ", "TotalPrice"},
{ "制单人", "Creator"},
{ "制单日期", "CreateDate"},
{ "单据状态", "BillStatus"},
{ "确认人", "Confirmor"},
{ "确认日期", "ConfirmDate"},
{ "结单人", "Closer"},
{ "结单日期", "CloseDate"},
{ "最后更新人", "ModifiedUserID"},
{ "最后更新日期", "ModifiedDate"},
{ "备注", "Remark"},
};
if (HiddenMoreUnit.Value == "True")
{
aDetail = new string[,]{
{ "物品编号", "ID"},
{ "物品名称", "ProductID" },
{ "批次", "BatchNo" },
{ "单位", "UsedUnitName"},
{ "基本单位", "UnitID" },
{ "基本数量", "AdjustCount" },
{ "调整类型", "AdjustType"},
{ "调整数量 ", "UsedUnitCount"},
{ "成本单价 ", "UsedPrice"},
{ "调整金额 ", "CostPriceTotal"},
{ "备注 ", "Remark"},
};
}
else
{
aDetail = new string[,]{
{ "物品编号", "ID"},
{ "物品名称", "ProductID" },
{ "批次", "BatchNo" },
{ "单位", "UnitID"},
{ "调整类型", "AdjustType"},
{ "调整数量 ", "AdjustCount"},
{ "成本单价 ", "CostPrice"},
{ "调整金额 ", "CostPriceTotal"},
{ "备注 ", "Remark"},
};
}
#region 1.扩展属性
int countExt = 0;
DataTable dtExtTable = XBase.Business.Office.SupplyChain.TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.StorageAdjust");
if (dtExtTable.Rows.Count > 0)
{
for (int i = 0; i < dtExtTable.Rows.Count; i++)
{
for (int x = 0; x < (aBase.Length / 2) - 15; x++)
{
if (x == i)
{
aBase[x, 0] = dtExtTable.Rows[i]["EFDesc"].ToString();
countExt++;
}
}
}
}
#endregion
DataTable dbPrint = XBase.Business.Common.PrintParameterSettingBus.GetPrintParameterSettingInfo(model);
DataTable dtMRP = XBase.Business.Office.StorageManager.StorageAdjustBus.GetAdjustInfo(intMrpID);
DataTable dtDetail = XBase.Business.Office.StorageManager.StorageAdjustBus.GetAdjustDetailInfo(intMrpID);
string strBaseFields = "";
string strDetailFields = "";
if (dbPrint.Rows.Count > 0)
{
#region 设置过打印模板设置时 直接取出表里设置的值
isSeted.Value = "1";
strBaseFields = dbPrint.Rows[0]["BaseFields"].ToString();
strDetailFields = dbPrint.Rows[0]["DetailFields"].ToString();
#endregion
}
else
{
#region 未设置过打印模板设置 默认显示所有的
isSeted.Value = "0";
/*未设置过打印模板设置时，默认显示的字段 基本信息字段*/
for (int m = 10; m < aBase.Length / 2; m++)
{
strBaseFields = strBaseFields + aBase[m, 1] + "|";
}
/*未设置过打印模板设置时，默认显示的字段 基本信息字段+扩展信息字段*/
if (countExt > 0)
{
for (int i = 0; i < countExt; i++)
{
strBaseFields = strBaseFields + "ExtField" + (i + 1) + "|";
}
}
/*未设置过打印模板设置时，默认显示的字段 明细信息字段*/
for (int n = 0; n < aDetail.Length / 2; n++)
{
strDetailFields = strDetailFields + aDetail[n, 1] + "|";
}
#endregion
}
#region 2.主表信息
if (!string.IsNullOrEmpty(strBaseFields))
{
tableBase.InnerHtml = WritePrintPageTable("日常调整单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, true);
}
#endregion
#region 3.明细信息
if (!string.IsNullOrEmpty(strDetailFields))
{
tableDetail.InnerHtml = WritePrintPageTable("日常调整单", strBaseFields.TrimEnd('|'), strDetailFields.TrimEnd('|'), aBase, aDetail, dtMRP, dtDetail, false);
}
#endregion
}
public static SqlCommand updateStorageProduct(string BatchNo, string ProductID, string StorageID, string ProductNum, StorageInitailModel model, bool flag)
{
//true 表示入库增加分仓存量数据
StringBuilder strSql = new StringBuilder();
if (flag == true)
{
strSql.AppendLine("update officedba.StorageProduct set ");
strSql.AppendLine("ProductCount=ISNULL(ProductCount,0)+@ProductNum ");
strSql.AppendLine(" where StorageID=@StorageID and ProductID=@ProductID and CompanyCD=@CompanyCD");
if (!string.IsNullOrEmpty(BatchNo))
{
strSql.Append(" and BatchNo =@BatchNo ");
}
else
{
strSql.Append(" and (BatchNo is null or BatchNo='') ");
}
}
//否则 表示（入库减少）分仓存量数据（修改的时候）
else
{
strSql.AppendLine("update officedba.StorageProduct set ");
strSql.AppendLine("ProductCount=ISNULL(ProductCount,0)-@ProductNum ");
strSql.AppendLine(" where StorageID=@StorageID and ProductID=@ProductID and CompanyCD=@CompanyCD");
if (!string.IsNullOrEmpty(BatchNo))
{
strSql.Append(" and BatchNo =@BatchNo ");
}
else
{
strSql.Append(" and (BatchNo is null or BatchNo='') ");
}
}
SqlCommand commRePD = new SqlCommand();
commRePD.CommandText = strSql.ToString();
commRePD.Parameters.AddWithValue("@ProductNum", decimal.Parse(ProductNum));
commRePD.Parameters.AddWithValue("@StorageID", StorageID);
commRePD.Parameters.AddWithValue("@ProductID", ProductID);
commRePD.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
if (!string.IsNullOrEmpty(BatchNo))
{
commRePD.Parameters.AddWithValue("@BatchNo", BatchNo);
}
return commRePD;
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
StorageInitailModel model = new StorageInitailModel();
string EnterDateStart = string.Empty;
string EnterDateEnd = string.Empty;
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.InNo = txtInNo.Value;
model.Title = txtTitle.Value;
model.DeptID = txtDeptID.Value;
model.StorageID = sltStorageID.SelectedValue;
model.Executor = txtExecutorID.Value;
model.BillStatus = sltBillStatus.Value;
EnterDateStart = txtEnterDateStart.Value;
EnterDateEnd = txtEnterDateEnd.Value;
string BatchNo = txtBatchNo.Value;
string orderBy = txtorderBy.Value;
if (!string.IsNullOrEmpty(orderBy))
{
if (orderBy.Split('_')[1] == "a")
{
orderBy = orderBy.Split('_')[0] + " asc";
}
else
{
orderBy = orderBy.Split('_')[0] + " desc";
}
}
DataTable dt = StorageInitailBus.GetStorageInitailTableBycondition(BatchNo,model, EnterDateStart, EnterDateEnd, orderBy);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "单据编号", "单据主题", "仓库", "入库部门", "入库人", "入库日期", "入库数量", "入库金额", "单据状态" },
new string[] { "InNo", "Title", "StorageName", "DeptName", "Executor", "EnterDate", "CountTotal", "TotalPrice", "BillStatus" },
"期初库存录入列表");
}
/// <summary>
/// 查询期初库存录入信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetStorageInitailTableBycondition(string BatchNo,string EFIndex,string EFDesc,StorageInitailModel model, string timeStart, string timeEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT DISTINCT a.ID");
sql.AppendLine(" ,ISNULL(a.InNo,'') AS InNo");
sql.AppendLine(",ISNULL(a.Title,'') AS Title");
sql.AppendLine(" ,ISNULL(b.StorageName,'') AS StorageName");
sql.AppendLine(" ,ISNULL(c.EmployeeName,'') AS Executor");
sql.AppendLine(" ,ISNULL(d.DeptName,'') AS DeptName");
sql.AppendLine(",case when a.EnterDate Is NULL then '' else CONVERT(VARCHAR(10),a.EnterDate, 21) end AS EnterDate");
sql.AppendLine(",case a.billStatus when '1' then '制单' when '2' then '执行' when '3' then '变更'");
sql.AppendLine(" when '4' then '手工结单' when '5' then '自动结单' end as BillStatus");
sql.AppendLine(" ,ISNULL(a.CountTotal,0) AS CountTotal");
sql.AppendLine(" ,ISNULL(a.TotalPrice,0) AS TotalPrice");
sql.AppendLine(" FROM officedba.StorageInitail a ");
sql.AppendLine(" left join officedba.EmployeeInfo c on a.Executor = c.ID");
sql.AppendLine(" left join officedba.DeptInfo d on a.DeptID = d.ID");
sql.AppendLine(" left join officedba.StorageInfo b on a.StorageID = b.ID");
sql.AppendLine(" left join officedba.UserInfo h on a.ModifiedUserID=h.UserID ");
sql.AppendLine(" left join officedba.StorageInitailDetail i on i.InNo = a.InNo ");
sql.AppendLine("where a.CompanyCD=@CompanyCD");
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
if (!string.IsNullOrEmpty(BatchNo))
{
sql.AppendLine(" and i.BatchNo like '%'+ @BatchNo +'%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo));
}
if (!string.IsNullOrEmpty(model.InNo))
{
sql.AppendLine(" and a.InNo like '%'+ @InNo +'%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@InNo", model.InNo));
}
if (!string.IsNullOrEmpty(model.Title))
{
sql.AppendLine(" and a.Title like '%'+ @Title +'%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));
}
if (!string.IsNullOrEmpty(model.StorageID))
{
sql.AppendLine(" and a.StorageID=@StorageID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID));
}
if (!string.IsNullOrEmpty(model.DeptID))
{
sql.AppendLine(" and a.DeptID=@DeptID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.DeptID));
}
if (!string.IsNullOrEmpty(model.Executor))
{
sql.AppendLine(" and a.Executor=@Executor");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Executor", model.Executor));
}
if (!string.IsNullOrEmpty(timeStart))
{
sql.AppendLine(" and a.EnterDate>=@timeStart");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@timeStart", timeStart));
}
if (!string.IsNullOrEmpty(timeEnd))
{
sql.AppendLine(" and a.EnterDate<=@timeEnd");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@timeEnd", timeEnd));
}
if (!string.IsNullOrEmpty(model.BillStatus))
{
sql.AppendLine(" and a.BillStatus=@BillStatus");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus));
}
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
sql.AppendLine(" and a.ExtField" + EFIndex + " like @EFDesc ");
comm.Parameters.Add(SqlHelper.GetParameter("@EFDesc", "%" + EFDesc + "%"));
}
comm.CommandText = sql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
