/// <summary>
/// 列表界面查询方法
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageCount">每页记录数</param>
/// <param name="orderBy">排序方法</param>
/// <param name="TotalCount">总记录数</param>
/// <param name="model">实体类</param>
/// <returns></returns>
public static DataTable SelectListData(int pageIndex, int pageCount, string orderBy, ref int TotalCount
, SubStorageAccountModel model)
{
SqlCommand comm = new SqlCommand();
StringBuilder sql = new StringBuilder(C_SELECT_ALL);
sql.Append(" WHERE 1=1 ");
comm.CommandText = sql.ToString();
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, orderBy, ref TotalCount));
}
/// <summary>
/// 初始化库存流水账--分店
/// </summary>
/// <returns></returns>
public static void InitInfo()
{
/*获取已开通且使用进销存的企业*/
DataTable CompanyDT = SubStorageInitDBHelper.GetDistinctCompany();
foreach (DataRow row in CompanyDT.Rows)
{
/*获取某企业使用进销存的商品及仓库对应的记录*/
DataTable ProAndStroDT = SubStorageInitDBHelper.GetDistinctProductAndStronge(row["CompanyCD"].ToString());
/*获取某企业使用进销存的日期记录*/
DataTable CompanyTimedt = SubStorageInitDBHelper.GetDistinctDate(row["CompanyCD"].ToString());
string BeginDate = string.Empty;//企业使用进销存的开始日期
string EndDate = string.Empty;
if (CompanyTimedt.Rows.Count > 0 && CompanyTimedt != null)
{
BeginDate = CompanyTimedt.Rows[0]["EnterDate"].ToString();
EndDate = CompanyTimedt.Rows[CompanyTimedt.Rows.Count - 1]["EnterDate"].ToString();
}
foreach (DataRow rowP in ProAndStroDT.Rows)
{
/*获取该企业对应的物品，仓库的所有操作记录，按日期顺序取数据*/
DataTable CompanyInfoDT = SubStorageInitDBHelper.GetCompanyInfo(row["CompanyCD"].ToString(), rowP["ProductID"].ToString(), rowP["StorageID"].ToString());
decimal ret = 0;//计算累计现有存量
foreach (DataRow rowCom in CompanyInfoDT.Rows)
{
SubStorageAccountModel model = new SubStorageAccountModel();
if (!GetType(rowCom["flag"].ToString()))
{
ret += Convert.ToDecimal(0 - Convert.ToDecimal(rowCom["ProductCount"].ToString()));
}
else
{
ret += Convert.ToDecimal(rowCom["ProductCount"].ToString()); //现有存量
}
model.BillNo = rowCom["NoCode"].ToString(); //业务单编号
model.BillType = int.Parse(rowCom["flag"].ToString()); //单据类型
model.CompanyCD = rowCom["CompanyCD"].ToString(); //企业编号
model.HappenCount = Convert.ToDecimal(rowCom["ProductCount"].ToString()); //出入库数量
model.HappenDate = Convert.ToDateTime(rowCom["EnterDate"].ToString()); //出入库时间
model.Price = Convert.ToDecimal(rowCom["Price"].ToString()); //单价
model.ProductID = int.Parse(rowCom["ProductID"].ToString()); //物品
model.DeptID = int.Parse(rowCom["StorageID"].ToString()); //仓库
model.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID; //创建人
model.ProductCount = ret; //现有存量
model.PageUrl = ""; //跳转URL
model.Remark = ""; //备注
SubStorageInitDBHelper.InsertStorageAccount(model); //插入库存流水账
}
}
}
}
/// <summary>
/// 插入操作的执行命令(并且获得现有库存量)
/// </summary>
/// <param name="model">实体类</param>
/// <returns>插入操作的执行命令</returns>
public static SqlCommand GetCountAndInsertCommand(SubStorageAccountModel model)
{
DataTable dt = GetSubProductCount(model.CompanyCD, model.ProductID.Value.ToString(), model.DeptID.Value.ToString(), model.BatchNo);
decimal count = 0m;
model.ProductCount = model.HappenCount;
if (dt.Rows.Count > 0 && decimal.TryParse(dt.Rows[0]["ProductCount"].ToString(), out count))
{
model.ProductCount = count + (model.ProductCount.HasValue ? model.ProductCount.Value : 0);
}
model.HappenCount = Math.Abs(model.HappenCount.HasValue ? model.HappenCount.Value : 0);
return(InsertCommand(model));
}
/// <summary>
/// 修改数据记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改操作的执行命令</returns>
public static SqlCommand UpdateCommand(SubStorageAccountModel model)
{
// SQL语句
SqlCommand comm = new SqlCommand();
comm.CommandText = C_UPDATE;
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
comm.Parameters.AddRange(parameters);
return(comm);
}
/// <summary>
/// 修改数据记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool Update(SubStorageAccountModel model)
{
bool returnValue = false; // 返回值
// SQL语句
StringBuilder sqlSentence = new StringBuilder(C_UPDATE);
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
//执行SQL
returnValue = SqlHelper.ExecuteTransSql(sqlSentence.ToString(), parameters) > 0;
return(returnValue);
}
/// <summary>
/// 插入操作的执行命令
/// </summary>
/// <param name="model">实体类</param>
/// <returns>插入操作的执行命令</returns>
public static SqlCommand InsertCommand(SubStorageAccountModel model)
{
// SQL语句
SqlCommand comm = new SqlCommand();
comm.CommandText = C_INSERT + " SET @IndexID = @@IDENTITY ";
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
comm.Parameters.AddRange(parameters);
SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int);
IndexID.Direction = ParameterDirection.Output;
comm.Parameters.Add(IndexID);
return(comm);
}
/// <summary>
/// 插入库存流水账
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertStorageAccount(SubStorageAccountModel model)
{
bool Rev = false;
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.SubStorageAccount(");
strSql.Append("CompanyCD,BillType,ProductID,DeptID,BillNo,HappenDate,HappenCount,ProductCount,Creator,Price,PageUrl,ReMark)");
strSql.Append(" values (");
strSql.Append("@CompanyCD,@BillType,@ProductID,@DeptID,@BillNo,@HappenDate,@HappenCount,@ProductCount,@Creator,@Price,@PageUrl,@ReMark)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@CompanyCD", SqlDbType.VarChar, 8),
new SqlParameter("@BillType", SqlDbType.Int, 4),
new SqlParameter("@ProductID", SqlDbType.Int, 4),
new SqlParameter("@DeptID", SqlDbType.Int, 4),
new SqlParameter("@BillNo", SqlDbType.VarChar, 50),
new SqlParameter("@HappenDate", SqlDbType.DateTime),
new SqlParameter("@HappenCount", SqlDbType.Decimal, 13),
new SqlParameter("@ProductCount", SqlDbType.Decimal, 13),
new SqlParameter("@Creator", SqlDbType.Int, 4),
new SqlParameter("@Price", SqlDbType.Decimal, 9),
new SqlParameter("@PageUrl", SqlDbType.VarChar, 500),
new SqlParameter("@ReMark", SqlDbType.VarChar, 500)
};
parameters[0].Value = model.CompanyCD;
parameters[1].Value = model.BillType;
parameters[2].Value = model.ProductID;
parameters[3].Value = model.DeptID;
parameters[4].Value = model.BillNo;
parameters[5].Value = model.HappenDate;
parameters[6].Value = model.HappenCount;
parameters[7].Value = model.ProductCount;
parameters[8].Value = model.Creator;
parameters[9].Value = model.Price;
parameters[10].Value = model.PageUrl;
parameters[11].Value = model.Remark;
Rev = SqlHelper.ExecuteTransSql(strSql.ToString(), parameters) > 0 ? true : false;
return(Rev);
}
/// <summary>
/// 插入数据记录
/// </summary>
///<param name="model">实体类</param>
/// <returns>插入数据是否成功:true成功,false不成功</returns>
public static bool Insert(SubStorageAccountModel model)
{
ArrayList sqlList = new ArrayList();
SqlCommand cmd = SubStorageAccountDBHelper.InsertCommand(model);
sqlList.Add(cmd);
if (SqlHelper.ExecuteTransWithArrayList(sqlList))
{
int i = 0;
if (int.TryParse(cmd.Parameters["@IndexID"].Value.ToString(), out i))
{
model.ID = i;
}
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 设置新增和修改的参数数组的值
/// </summary>
/// <param name="parameters">参数数组</param>
/// <param name="model">实体类</param>
/// <returns>返回参数数组</returns>
private static SqlParameter[] SetInsertAndUpdateParametersValue(SqlParameter[] parameters, SubStorageAccountModel model)
{
if (!model.ID.HasValue)
{
parameters[m_iDCol].Value = System.DBNull.Value;
}
else
{
parameters[m_iDCol].Value = model.ID; // ID，自动生成
}
parameters[m_companyCDCol].Value = model.CompanyCD; // 公司编码
if (!model.DeptID.HasValue)
{
parameters[m_deptIDCol].Value = System.DBNull.Value;
}
else
{
parameters[m_deptIDCol].Value = model.DeptID; // 分店ID
}
if (!model.BillType.HasValue)
{
parameters[m_billTypeCol].Value = System.DBNull.Value;
}
else
{
parameters[m_billTypeCol].Value = model.BillType; // 单据类型
}
if (!model.ProductID.HasValue)
{
parameters[m_productIDCol].Value = System.DBNull.Value;
}
else
{
parameters[m_productIDCol].Value = model.ProductID; // 物品ID
}
parameters[m_batchNoCol].Value = model.BatchNo; // 批次
parameters[m_billNoCol].Value = model.BillNo; // 单据编号
if (!model.HappenDate.HasValue)
{
parameters[m_happenDateCol].Value = System.DBNull.Value;
}
else
{
parameters[m_happenDateCol].Value = model.HappenDate; // 出入库时间
}
if (!model.HappenCount.HasValue)
{
parameters[m_happenCountCol].Value = System.DBNull.Value;
}
else
{
parameters[m_happenCountCol].Value = model.HappenCount; // 出入库数量
}
if (!model.ProductCount.HasValue)
{
parameters[m_productCountCol].Value = System.DBNull.Value;
}
else
{
parameters[m_productCountCol].Value = model.ProductCount; // 现有存量
}
if (!model.Creator.HasValue)
{
parameters[m_creatorCol].Value = System.DBNull.Value;
}
else
{
parameters[m_creatorCol].Value = model.Creator; // 业务操作人(取当前登录人的ID)
}
parameters[m_pageUrlCol].Value = model.PageUrl; // 页面链接地址
if (!model.Price.HasValue)
{
parameters[m_priceCol].Value = System.DBNull.Value;
}
else
{
parameters[m_priceCol].Value = model.Price; // 单价
}
parameters[m_remarkCol].Value = model.Remark; // 备注
return(parameters);
}
/// <summary>
/// 列表界面查询方法
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageCount">每页记录数</param>
/// <param name="orderBy">排序方法</param>
/// <param name="TotalCount">总记录数</param>
/// <param name="model">实体类</param>
/// <returns></returns>
public static DataTable SelectListData(int pageIndex, int pageCount, string orderBy, ref int TotalCount
, SubStorageAccountModel model)
{
return(SubStorageAccountDBHelper.SelectListData(pageIndex, pageCount, orderBy, ref TotalCount, model));
}
/// <summary>
/// 修改数据记录
/// </summary>
///<param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool Update(SubStorageAccountModel model)
{
return(SubStorageAccountDBHelper.Update(model));
}
/// <summary>
/// 确认
/// </summary>
/// <param name="SubSellOrderM"></param>
/// <param name="SubSellOrderDetailMList"></param>
/// <returns></returns>
public static bool ConfirmOutSubSellOrder(SubSellOrderModel SubSellOrderM, List <SubSellOrderDetailModel> SubSellOrderDetailMList)
{
try
{
ArrayList lstConfirm = new ArrayList();
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
int id = 0;
decimal count = 0m;
if (SubSellOrderM.SendMode == "1")
{//分店发货
//确认时，更新分店存量表
foreach (SubSellOrderDetailModel SubSellOrderDetailM in SubSellOrderDetailMList)
{
#region 添加门店库存流水帐
SubStorageAccountModel aModel = new SubStorageAccountModel();
aModel.BatchNo = SubSellOrderDetailM.BatchNo;
aModel.BillNo = SubSellOrderM.OrderNo;
aModel.BillType = 3;
aModel.CompanyCD = SubSellOrderDetailM.CompanyCD;
aModel.PageUrl = "";
if (int.TryParse(SubSellOrderM.Creator, out id))
{
aModel.Creator = id;
}
if (int.TryParse(SubSellOrderM.DeptID, out id))
{
aModel.DeptID = id;
}
aModel.HappenDate = DateTime.Now;
if (int.TryParse(SubSellOrderDetailM.ProductID, out id))
{
aModel.ProductID = id;
}
if (decimal.TryParse(SubSellOrderDetailM.UnitPrice, out count))
{
aModel.Price = count;
}
if (decimal.TryParse(SubSellOrderDetailM.ProductCount, out count))
{
aModel.HappenCount = -count;
}
aModel.PageUrl = SubSellOrderM.Remark;
lstConfirm.Add(XBase.Data.Office.SubStoreManager.SubStorageAccountDBHelper.GetCountAndInsertCommand(aModel));
#endregion
lstConfirm.Add(XBase.Data.Office.LogisticsDistributionManager.StorageProductQueryDBHelper.UpdateProductCount(userInfo.CompanyCD
, SubSellOrderDetailM.ProductID, SubSellOrderM.DeptID, SubSellOrderDetailM.BatchNo, -count));
}
}
else if (SubSellOrderM.SendMode == "2")
{//总部发货
//确认时更新分仓存量表
foreach (SubSellOrderDetailModel SubSellOrderDetailM in SubSellOrderDetailMList)
{
#region 添加库存流水帐
XBase.Model.Office.StorageManager.StorageAccountModel sModel = new XBase.Model.Office.StorageManager.StorageAccountModel();
sModel.BatchNo = SubSellOrderDetailM.BatchNo;
sModel.BillNo = SubSellOrderM.OrderNo;
sModel.BillType = 21;
sModel.CompanyCD = SubSellOrderDetailM.CompanyCD;
if (int.TryParse(SubSellOrderDetailM.StorageID, out id))
{
sModel.StorageID = id;
}
if (int.TryParse(SubSellOrderM.Creator, out id))
{
sModel.Creator = id;
}
if (int.TryParse(SubSellOrderDetailM.ProductID, out id))
{
sModel.ProductID = id;
}
if (decimal.TryParse(SubSellOrderDetailM.UnitPrice, out count))
{
sModel.Price = count;
}
if (decimal.TryParse(SubSellOrderDetailM.ProductCount, out count))
{
sModel.HappenCount = count;
}
sModel.HappenDate = DateTime.Now;
sModel.PageUrl = SubSellOrderM.Remark;
lstConfirm.Add(XBase.Data.Office.StorageManager.StorageAccountDBHelper.InsertStorageAccountCommand(sModel, "1"));
#endregion
// 更新库存
lstConfirm.Add(XBase.Data.Office.StorageManager.StorageSearchDBHelper.UpdateProductCount(userInfo.CompanyCD
, SubSellOrderDetailM.ProductID, SubSellOrderDetailM.StorageID
, SubSellOrderDetailM.BatchNo, -count));
}
}
//更新发货信息和安装信息
lstConfirm.Add(SubSellOrderDBHelper.ConfirmOutSubSellOrder(SubSellOrderM));
//获取登陆用户信息
//UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//定义返回变量
bool isSucc = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//执行插入操作
try
{
isSucc = SqlHelper.ExecuteTransWithArrayList(lstConfirm);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (isSucc)
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
LogInfoModel logModel = InitLogInfo(SubSellOrderM.OrderNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_OUT;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return(isSucc);
}
catch (Exception ex)
{
throw ex;
}
}