/// <summary>
/// 添加新单据
/// </summary>
/// <returns></returns>
public static bool Insert(VoucherTemplateModel voucherTemplateModel, List<VoucherTemplateDetailModel> voucherTemplateDetailModelList, out string strMsg, out int Id)
{
bool isSucc = false;//是否添加成功
strMsg = "";
Id = 0;
//判断单据编号是否存在
if (NoIsExist(voucherTemplateModel.TemNo))
{
if (IsTypeUsed(voucherTemplateModel.TemType.ToString(), voucherTemplateModel.CompanyCD))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertVoucherTemplate(voucherTemplateModel, tran, out Id);
InsertVoucherTemplateDetail(voucherTemplateDetailModelList, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该模板类型已存在，请选择其他未使用的模板类型！";
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
public static bool Update(VoucherTemplateModel voucherTemplateModel, List<VoucherTemplateDetailModel> voucherTemplateDetailModellList, out string strMsg)
{
bool isSucc = false;//是否添加成功
//定义变量
string remark = string.Empty;
strMsg = "";
try
{
isSucc = VoucherTemaplateDBHelper.Update(voucherTemplateModel, voucherTemplateDetailModellList, out strMsg); ;
//设置操作成功标识
//remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
catch
{
////输出日志
//SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLORDER_ADD);
////设置操作成功标识
//remark = ConstUtil.LOG_PROCESS_FAILED;
}
//SellLogCommon.InsertLog(voucherTemplateModel.OrderNo, ConstUtil.MODULE_ID_SELLORDER_ADD, ConstUtil.CODING_RULE_TABLE_SELLORDER, remark, ConstUtil.LOG_PROCESS_UPDATE);
return isSucc;
}
public static DataTable GetFeesBySearch(string CompanyCD, VoucherTemplateModel FeesM, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return VoucherTemaplateDBHelper.GetFeesBySearch(CompanyCD, FeesM, pageIndex, pageCount, ord, ref TotalCount);
}
public static DataTable ExportFeesBySearch(string CompanyCD, VoucherTemplateModel FeesM)
{
return VoucherTemaplateDBHelper.ExportFeesBySearch(CompanyCD, FeesM);
}
public static DataTable ExportFeesBySearch(string CompanyCD, VoucherTemplateModel VoucherTemplateM)
{
try
{
#region 查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("SELECT a.ID ");
searchSql.AppendLine(" ,a.CompanyCD ");
searchSql.AppendLine(" ,a.TemNo,a.TemName ");
searchSql.AppendLine(",a.Abstract ");
searchSql.AppendLine(",case (a.TemType) ");
searchSql.AppendLine(" when '1' then '采购订单' ");
searchSql.AppendLine(" when '2' then '销售订单' ");
searchSql.AppendLine(" when '3' then '委托代销单' ");
searchSql.AppendLine(" when '4' then '销售退货单' ");
searchSql.AppendLine(" when '5' then '采购入库' ");
searchSql.AppendLine(" when '6' then '其他出库单' ");
searchSql.AppendLine(" when '7' then '销售出库单' ");
searchSql.AppendLine(" when '8' then '其他入库单' ");
searchSql.AppendLine(" when '9' then '收款单' ");
searchSql.AppendLine(" when '10' then '付款单' ");
searchSql.AppendLine(" when '11' then '销售发货通知单' ");
searchSql.AppendLine(" end as TemTypeName ");
searchSql.AppendLine(" ,a.Remark ");
searchSql.AppendLine(" , CONVERT(varchar(100), a.CreateDate, 23) CreateDate ");
searchSql.AppendLine(" ,a.TemType,a.UsedStatus,case(a.UsedStatus) when '1' then '启用' when '0' then '停用' end as UsedStatusName,b.EmployeeName as CreatorName,a.Creator ");
searchSql.AppendLine(" FROM officedba.VoucherTemplate a ");
searchSql.AppendLine(" left join officedba.EmployeeInfo b on b.id = a.Creator ");
searchSql.AppendLine(" WHERE a.CompanyCD = @CompanyCD ");
#endregion
#region 定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", CompanyCD));
//模板编号
if (!string.IsNullOrEmpty(VoucherTemplateM.TemNo))
{
searchSql.AppendLine(" AND a.TemNo LIKE '%' + @TemNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemNo", VoucherTemplateM.TemNo));
}
//摘要
if (!string.IsNullOrEmpty(VoucherTemplateM.Abstract))
{
searchSql.AppendLine(" AND a.Abstract LIKE '%' + @Abstract + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Abstract", VoucherTemplateM.Abstract));
}
//模板类别
if (VoucherTemplateM.TemType != 0)
{
searchSql.AppendLine(" AND a.TemType=@TemType ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TemType", VoucherTemplateM.TemType.ToString()));
}
#endregion
//设定comm的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
catch
{
return null;
}
}
/// <summary>
/// 跟新主表数据
/// </summary>
/// <param name="voucherTemplateModel"></param>
/// <param name="tran"></param>
private static void UpdateVoucherTemplate(VoucherTemplateModel voucherTemplateModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
#region sql语句
strSql.Append("update officedba.VoucherTemplate set ");
strSql.Append("TemName=@TemName,");
strSql.Append("TemType=@TemType,");
strSql.Append("Abstract=@Abstract,");
strSql.Append("Remark=@Remark,");
strSql.Append("UsedStatus=@UsedStatus,");
strSql.Append("Creator=@Creator,");
strSql.Append("CreateDate=@CreateDate");
strSql.Append(" where CompanyCD=@CompanyCD and TemNo =@TemNo ");
#endregion
SqlParameter[] param = null;
ArrayList lcmd = new ArrayList();
#region 参数
lcmd.Add(SqlHelper.GetParameterFromString("@TemName", voucherTemplateModel.TemName));
lcmd.Add(SqlHelper.GetParameterFromString("@TemType", voucherTemplateModel.TemType.ToString()));
lcmd.Add(SqlHelper.GetParameterFromString("@Abstract", voucherTemplateModel.Abstract));
lcmd.Add(SqlHelper.GetParameterFromString("@Remark", voucherTemplateModel.Remark));
lcmd.Add(SqlHelper.GetParameterFromString("@UsedStatus", voucherTemplateModel.UsedStatus));
lcmd.Add(SqlHelper.GetParameterFromString("@Creator", voucherTemplateModel.Creator.ToString()));
lcmd.Add(SqlHelper.GetParameterFromString("@CreateDate", voucherTemplateModel.CreateDate.ToString()));
lcmd.Add(SqlHelper.GetParameterFromString("@CompanyCD", voucherTemplateModel.CompanyCD));
lcmd.Add(SqlHelper.GetParameterFromString("@TemNo ", voucherTemplateModel.TemNo));
#endregion
if (lcmd != null && lcmd.Count > 0)
{
param = new SqlParameter[lcmd.Count];
for (int i = 0; i < lcmd.Count; i++)
{
param[i] = (SqlParameter)lcmd[i];
}
}
foreach (SqlParameter para in param)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), param);
}
public static bool Update(VoucherTemplateModel voucherTemplateModel, List<VoucherTemplateDetailModel> voucherTemplateDetailModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
string strSql = "delete from officedba.VoucherTemplateDetail where TemNo =@TemNo and CompanyCD=@CompanyCD";
SqlParameter[] paras = { new SqlParameter("@TemNo ", voucherTemplateModel.TemNo), new SqlParameter("@CompanyCD", voucherTemplateModel.CompanyCD) };
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateVoucherTemplate(voucherTemplateModel, tran);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), paras);
InsertVoucherTemplateDetail(voucherTemplateDetailModelList, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
return isSucc;
}
/// <summary> .
/// 为主表插入数据
/// </summary>
/// <param name="voucherTemplateModel"></param>
/// <param name="tran"></param>
private static void InsertVoucherTemplate(VoucherTemplateModel voucherTemplateModel, TransactionManager tran, out int IntID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.VoucherTemplate(");
strSql.Append("CompanyCD,TemNo ,TemName,TemType,Abstract,Remark,UsedStatus,Creator,CreateDate)");
strSql.Append(" values (");
strSql.Append("@CompanyCD,@TemNo ,@TemName,@TemType,@Abstract,@Remark,@UsedStatus,@Creator,@CreateDate)");
strSql.Append(" set @IntID= @@IDENTITY");
#region 参数
SqlParameter[] parms = new SqlParameter[10];
parms[0] = SqlHelper.GetParameter("@CompanyCD", voucherTemplateModel.CompanyCD);
parms[1] = SqlHelper.GetParameter("@TemNo ", voucherTemplateModel.TemNo);
parms[2] = SqlHelper.GetParameter("@TemName", voucherTemplateModel.TemName);
parms[3] = SqlHelper.GetParameter("@TemType", voucherTemplateModel.TemType.ToString());
parms[4] = SqlHelper.GetParameter("@Abstract", voucherTemplateModel.Abstract);
parms[5] = SqlHelper.GetParameter("@Remark", voucherTemplateModel.Remark);
parms[6] = SqlHelper.GetParameter("@UsedStatus", voucherTemplateModel.UsedStatus);
parms[7] = SqlHelper.GetParameter("@Creator", voucherTemplateModel.Creator.ToString());
parms[8] = SqlHelper.GetParameter("@CreateDate", voucherTemplateModel.CreateDate.ToString());
parms[9] = SqlHelper.GetOutputParameter("@IntID", SqlDbType.Int);
#endregion
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parms);
IntID = Convert.ToInt32(parms[9].Value);
}