/// <summary>
/// 修改销售报价单
/// </summary>
/// <param name="sellChanceModel">销售机会表实体</param>
/// <param name="sellChancePushModel">销售阶段表实体</param>
/// <returns>是否添加成功</returns>
public static bool UpdateOrder(Hashtable ht,SellOfferModel sellOfferModel, List<SellOfferDetailModel> SellOrderDetailModelList,
List<SellOfferHistoryModel> sellOfferHistoryModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
//定义变量
string remark = string.Empty;
strMsg = "";
try
{
isSucc = SellOfferDBHelper.UpdateOrder(ht,sellOfferModel, SellOrderDetailModelList, sellOfferHistoryModelList, out strMsg);
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
catch (Exception ex)
{
//输出日志
SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLOFFER_ADD);
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
SellLogCommon.InsertLog(sellOfferModel.OfferNo, ConstUtil.MODULE_ID_SELLOFFER_ADD, ConstUtil.CODING_RULE_TABLE_SELLOFFER, remark, ConstUtil.LOG_PROCESS_UPDATE);
return isSucc;
}
/// <summary>
/// 添加销售报价单
/// </summary>
/// <param name="sellChanceModel">销售机会表实体</param>
/// <param name="sellChancePushModel">销售阶段表实体</param>
/// <returns>是否添加成功</returns>
public static bool InsertOrder(Hashtable ht, SellOfferModel sellOfferModel, List<SellOfferDetailModel> SellOrderDetailModelList,
List<SellOfferHistoryModel> sellOfferHistoryModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
//判断单据编号是否存在
if (NoIsExist(sellOfferModel.OfferNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertSellOffer(sellOfferModel, tran);
//拓展属性
GetExtAttrCmd(sellOfferModel, ht, tran);
InsertSellOfferDetail(sellOfferModel, SellOrderDetailModelList, tran);
InsertSellOfferHistory(sellOfferModel, sellOfferHistoryModelList, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
isSucc = false;
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "该编号已被使用，请输入未使用的编号！";
}
return isSucc;
}
/// <summary>
/// 修改销售报价单
/// </summary>
/// <param name="sellChanceModel">销售机会表实体</param>
/// <param name="sellChancePushModel">销售阶段表实体</param>
/// <returns>是否添加成功</returns>
public static bool UpdateOrder(Hashtable ht,SellOfferModel sellOfferModel, List<SellOfferDetailModel> SellOrderDetailModelList,
List<SellOfferHistoryModel> sellOfferHistoryModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
if (IsUpdate(sellOfferModel.OfferNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateSellOffer(sellOfferModel, tran);
//拓展属性
GetExtAttrCmd(sellOfferModel, ht, tran);
InsertSellOfferDetail(sellOfferModel, SellOrderDetailModelList, tran);
InsertSellOfferHistory(sellOfferModel, sellOfferHistoryModelList, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
isSucc = false;
strMsg = "保存失败，请联系系统管理员！";
throw ex;
}
}
else
{
isSucc = false;
strMsg = "非制单状态的未提交审批、审批未通过或撤销审批单据不可修改！";
}
return isSucc;
}
protected void LoadPrintInfo()
{
PrintParameterSettingModel model = new PrintParameterSettingModel();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.BillTypeFlag = int.Parse(ConstUtil.BILL_TYPEFLAG_SALE);
model.PrintTypeFlag = ConstUtil.PRINTBILL_SELLCONTRANCT;
SellOfferModel modelMRP = new SellOfferModel();
modelMRP.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
modelMRP.OfferNo = this.OfferNo;
/*此处需注意在模板设置表里的字段和取基本信息的字段是否一致*/
string[,] aBase = {
{ "{ExtField1}", "ExtField1"},
{ "{ExtField2}", "ExtField2"},
{ "{ExtField3}", "ExtField3"},
{ "{ExtField4}", "ExtField4"},
{ "{ExtField5}", "ExtField5"},
{ "{ExtField6}", "ExtField6"},
{ "{ExtField7}", "ExtField7"},
{ "{ExtField8}", "ExtField8"},
{ "{ExtField9}", "ExtField9"},
{ "{ExtField10}", "ExtField10"},
{ "单据编号", "ContractNo"},
{ "主题", "Title"},
{ "源单类型", "FromTypeText" },
{ "源单编号", "OfferNo" },
{ "客户名称", "CustName"},
{ "客户电话", "CustTel"},
{ "业务类型", "BusiTypeName"},
{ "销售类别", "SellTypeName"},
{ "结算方式", "PayTypeName"},
{ "支付方式", "MoneyTypeName"},
{ "交货方式", "TakeTypeName"},
{ "运送方式", "CarryTypeName"},
{ "币种", "CurrencyName"},
{ "汇率", "Rate"},
{ "业务员", "SellerName"},
{ "部门", "DeptName"},
{ "客户方代表", "TheyDelegate"},
{ "我方代表", "OurDelegateName"},
{ "签约时间", "SignDate"},
{ "签约地点", "SignAddr"},
{ "开始日期 ", "StartDate"},
{ "截止日期", "EndDate"},
{ "洽谈进展", "TalkProcess"},
{ "合同状态", "stateText"},
{ "终止原因", "EndNote"},
{ "是否增值税", "isAddTaxName"},
//{ "是否被引用 ", "RealTotal"},
{ "金额合计", "TotalPrice"},
{ "税额合计", "Tax"},
{ "含税金额合计", "TotalFee"},
{ "整单折扣(%) ", "Discount"},
{ "折后含税金额", "RealTotal"},
{ "折扣金额合计", "DiscountTotal"},
{ "合同数量合计", "CountTotal"},
{ "单据状态", "BillStatusText"},
{ "制单人", "CreatorName"},
{ "制单日期", "CreateDate"},
{ "确认人", "ConfirmorName"},
{ "确认日期", "ConfirmDate"},
{ "结单人", "CloserName"},
{ "结单日期", "CloseDate"},
{ "最后更新人", "ModifiedUserID"},
{ "最后更新日期 ", "ModifiedDate"},
{ "备注", "Remark"},
};
string[,] aDetail;
//多计量单位
if (((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsMoreUnit)
{
aDetail = new string[,]{
{ "物品编号", "ProdNo"},
{ "物品名称", "ProductName"},
{ "规格", "Specification"},
{ "基本单位", "CodeName" },
{ "基本数量", "ProductCount"},
{ "单位", "UsedUnitName" },
{ "数量", "UsedUnitCount"},
{ "交货期限(天)", "SendTime" },
{ "包装要求", "TypeName"},
{ "单价", "UsedPrice"},
{ "含税价", "TaxPrice"},
{ "折扣(%)", "Discount"},
{ "税率(%)", "TaxRate"},
{ "含税金额", "TotalFee"},
{ "金额", "TotalPrice"},
{ "税额", "TotalTax"},
{ "备注 ", "Remark"},
};
}
else
{
aDetail = new string[,]{
{ "物品编号", "ProdNo"},
{ "物品名称", "ProductName"},
{ "规格", "Specification"},
{ "单位", "CodeName" },
{ "数量", "ProductCount"},
{ "交货期限(天)", "SendTime" },
{ "包装要求", "TypeName"},
{ "单价", "UnitPrice"},
{ "含税价", "TaxPrice"},
{ "折扣(%)", "Discount"},
{ "税率(%)", "TaxRate"},
{ "含税金额", "TotalFee"},
{ "金额", "TotalPrice"},
{ "税额", "TotalTax"},
{ "备注 ", "Remark"},
};
}
#region 1.扩展属性
int countExt = 0;
DataTable dtExtTable = TableExtFieldsBus.GetAllList(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, "", "officedba.SellContract");
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 = SellContractBus.GetRepOrder(OfferNo); //SellOfferBus.GetRepOrder(OfferNo);
DataTable dtDetail = SellContractBus.GetRepOrderDetail(OfferNo); //SellOfferBus.GetRepOrderDetail(OfferNo);
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 = "ContractNo|Title|FromTypeText|OfferNo|CustName|CustTel|BusiTypeName|SellTypeName|PayTypeName|MoneyTypeName|TakeTypeName|CarryTypeName|" +
"CurrencyName|Rate|SellerName|DeptName|TheyDelegate|OurDelegateName|SignDate|SignAddr|StartDate|EndDate|TalkProcess|stateText|EndNote|isAddTaxName|" +
"TotalPrice|Tax|TotalFee|Discount|RealTotal|DiscountTotal|CountTotal|BillStatusText|CreatorName|CreateDate|ConfirmorName|ConfirmDate|CloserName|" +
"CloseDate|ModifiedUserID|ModifiedDate|Remark";
/*基本信息字段+扩展信息字段*/
if (countExt > 0)
{
for (int i = 0; i < countExt; i++)
{
strBaseFields = strBaseFields + "|" + "ExtField" + (i + 1);
}
}
//多计量单位
if (((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsMoreUnit)
{
strDetailFields = "ProdNo|ProductName|Specification|CodeName|ProductCount|UsedUnitName|UsedUnitCount|SendTime|TypeName|UsedPrice|TaxPrice|Discount|" +
"TaxRate|TotalFee|TotalPrice|TotalTax|Remark";
}
else
{
strDetailFields = "ProdNo|ProductName|Specification|CodeName|ProductCount|SendTime|TypeName|UnitPrice|TaxPrice|Discount|" +
"TaxRate|TotalFee|TotalPrice|TotalTax|Remark";
}
}
#region 主表信息
if (!string.IsNullOrEmpty(strBaseFields))
{
tableBase.InnerHtml = WritePrintPageTable("销售合同", strBaseFields, strDetailFields, aBase, aDetail, dtMRP, dtDetail, true);
}
#endregion
#region 明细信息
if (!string.IsNullOrEmpty(strDetailFields))
{
tableDetail.InnerHtml = WritePrintPageTable("销售合同", strBaseFields, strDetailFields, aBase, aDetail, dtMRP, dtDetail, false);
}
#endregion
}
/// <summary>
/// 获取报价单列表
/// </summary>
/// <param name="sellOfferModel">sellOfferModel表实体</param>
/// <param name="FlowStatus">审批状态</param>
/// <returns></returns>
public static DataTable GetOrderList(string EFIndex, string EFDesc, SellOfferModel sellOfferModel, int? FlowStatus, DateTime? dt, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
string strSql = string.Empty;
strSql = "select * from ( SELECT so.ID,so.OfferNo, so.Title, so.TotalFee,so.ModifiedDate, isnull( e.EmployeeName,'') as EmployeeName, ";
strSql += "CONVERT(varchar(100),so.OfferDate, 23) AS OfferDate , so.QuoteTime, isnull(sc.ChanceNo,'') as ChanceNo, ";
strSql += "isnull(case ((select count(1) from officedba.SellContract where FromType='1' and FromBillID=so.ID)+ ";
strSql += "(select count(1) from officedba.SellOrder where FromType='1' and FromBillID=so.ID)) ";
strSql += "when 0 then '无引用' end , '被引用')as RefText, ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = so.ID AND BillTypeFlag = 5 AND BillTypeCode = 1 ";
strSql += "ORDER BY ModifiedDate DESC) AS FlowStatus , ";
strSql += "CASE so.FromType WHEN 0 THEN '无来源' WHEN 1 THEN '销售机会' END AS FromTypeText, ";
strSql += "CASE so.BillStatus WHEN 1 THEN '制单' WHEN 2 THEN '执行' WHEN 3 ";
strSql += "THEN '变更' WHEN 4 THEN '手工结单' WHEN 5 THEN '自动结单' END AS BillStatusText, ";
strSql += "CASE WHEN (SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = so.ID AND BillTypeFlag = 5 AND BillTypeCode = 1 ";
strSql += "ORDER BY ModifiedDate DESC) IS NULL THEN '' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = so.ID AND BillTypeFlag = 5 AND BillTypeCode = 1 ";
strSql += "ORDER BY ModifiedDate DESC) = 1 THEN '待审批' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = so.ID AND BillTypeFlag = 5 AND BillTypeCode = 1 ";
strSql += "ORDER BY ModifiedDate DESC) = 2 THEN '审批中' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = so.ID AND BillTypeFlag = 5 AND BillTypeCode = 1 ";
strSql += "ORDER BY ModifiedDate DESC) = 3 THEN '审批通过' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = so.ID AND BillTypeFlag = 5 AND BillTypeCode = 1 ";
strSql += "ORDER BY ModifiedDate DESC) = 4 THEN '审批不通过' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = so.ID AND BillTypeFlag = 5 AND BillTypeCode = 1 ";
strSql += "ORDER BY ModifiedDate DESC) = 5 THEN '撤销审批' END AS FlowInstanceText ";
strSql += "FROM officedba.SellOffer as so LEFT OUTER JOIN ";
strSql += "officedba.SellChance as sc on so.FromBillID=sc.ID LEFT OUTER JOIN ";
strSql += "officedba.EmployeeInfo as e ON so.Seller = e.ID where 1=1 and so.CompanyCD=@CompanyCD ";
strSql += " and ( charindex('," + sellOfferModel.Creator + ",' , ','+so.CanViewUser+',')>0 or so.Creator=" + sellOfferModel.Creator + " OR so.CanViewUser='' OR so.CanViewUser is null) ";
string strCompanyCD = string.Empty;//单位编号
strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
ArrayList arr = new ArrayList();
arr.Add(new SqlParameter("@CompanyCD", strCompanyCD));
if (sellOfferModel.BillStatus != null)
{
strSql += " and so.BillStatus= @BillStatus";
arr.Add(new SqlParameter("@BillStatus", sellOfferModel.BillStatus));
}
if (sellOfferModel.FromBillID != null)
{
strSql += " and so.FromBillID=@FromBillID";
arr.Add(new SqlParameter("@FromBillID", sellOfferModel.FromBillID)); ;
}
if (sellOfferModel.FromType != null)
{
strSql += " and so.FromType=@FromType";
arr.Add(new SqlParameter("@FromType", sellOfferModel.FromType));
}
if (sellOfferModel.OfferDate != null)
{
strSql += " and so.OfferDate >= @OfferDate";
arr.Add(new SqlParameter("@OfferDate", sellOfferModel.OfferDate));
}
if (dt != null)
{
strSql += " and so.OfferDate <= @OfferDate1";
arr.Add(new SqlParameter("@OfferDate1", dt));
}
if (sellOfferModel.OfferNo != null)
{
strSql += " and so.OfferNo like @OfferNo";
arr.Add(new SqlParameter("@OfferNo", "%" + sellOfferModel.OfferNo + "%"));
}
if (sellOfferModel.Seller != null)
{
strSql += " and so.Seller=@Seller";
arr.Add(new SqlParameter("@Seller", sellOfferModel.Seller));
}
if (sellOfferModel.Title != null)
{
strSql += " and so.Title like @Title";
arr.Add(new SqlParameter("@Title", "%" + sellOfferModel.Title + "%"));
}
if (sellOfferModel.CustID != null)
{
strSql += " and so.CustID=@CustID";
arr.Add(new SqlParameter("@CustID", sellOfferModel.CustID));
}
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
strSql+= " and so.ExtField" + EFIndex + " LIKE @EFDesc";
arr.Add(new SqlParameter("@EFDesc", "%" + EFDesc + "%"));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%"));
}
strSql += " ) as f where 1=1 ";
if (FlowStatus != null)
{
if (FlowStatus != 0)
{
strSql += " and f.FlowStatus=@FlowStatus";
arr.Add(new SqlParameter("@FlowStatus", FlowStatus));
}
else
{
strSql += " and f.FlowStatus is null ";
}
}
return SqlHelper.CreateSqlByPageExcuteSqlArr(strSql.ToString(), pageIndex, pageCount, ord, arr, ref TotalCount);
}
/// <summary>
/// 添加销售报价记录
/// </summary>
/// <param name="sellOfferModel"></param>
private static void InsertSellOfferHistory(SellOfferModel sellOfferModel, List<SellOfferHistoryModel> sellOfferHistoryModelList, TransactionManager tran)
{
if (sellOfferHistoryModelList.Count > 0)
{
string strSqlDel = "delete from officedba.SellOfferHistory where OfferNo=@OfferNo and CompanyCD=@CompanyCD and OfferTime=@OfferTime";
SqlParameter[] paras = { new SqlParameter("@OfferNo", sellOfferHistoryModelList[0].OfferNo),
new SqlParameter("@CompanyCD", sellOfferHistoryModelList[0].CompanyCD) ,
new SqlParameter("@OfferTime",sellOfferHistoryModelList[0].OfferTime)};
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSqlDel.ToString(), paras);
}
foreach (SellOfferHistoryModel sellOfferHistoryModel in sellOfferHistoryModelList)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.SellOfferHistory(");
strSql.Append("CompanyCD,OfferNo,ProductID,OfferDate,OfferTime,Seller,TotalPrice)");
strSql.Append(" values (");
strSql.Append("@CompanyCD,@OfferNo,@ProductID,getdate(),@OfferTime,@Seller,@TotalPrice)");
#region 参数
SqlParameter[] parameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@OfferNo", SqlDbType.VarChar,50),
new SqlParameter("@ProductID", SqlDbType.Int,4),
new SqlParameter("@OfferTime", SqlDbType.Int,4),
new SqlParameter("@Seller", SqlDbType.Int,4),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9)};
parameters[0].Value = sellOfferHistoryModel.CompanyCD;
parameters[1].Value = sellOfferHistoryModel.OfferNo;
parameters[2].Value = sellOfferHistoryModel.ProductID;
parameters[3].Value = sellOfferHistoryModel.OfferTime;
parameters[4].Value = sellOfferHistoryModel.Seller;
parameters[5].Value = sellOfferHistoryModel.TotalPrice;
foreach (SqlParameter para in parameters)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
#endregion
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters);
}
}
/// <summary>
/// 添加销售报价单子表信息
/// </summary>
/// <param name="sellOfferModel"></param>
private static void InsertSellOfferDetail(SellOfferModel sellOfferModel, List<SellOfferDetailModel> SellOrderDetailModelList, TransactionManager tran)
{
string strSqlDel = "delete from officedba.SellOfferDetail where OfferNo=@OfferNo and CompanyCD=@CompanyCD ";
SqlParameter[] paras = { new SqlParameter("@OfferNo", sellOfferModel.OfferNo), new SqlParameter("@CompanyCD", sellOfferModel.CompanyCD) };
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSqlDel.ToString(), paras);
foreach (SellOfferDetailModel sellOfferDetailModel in SellOrderDetailModelList)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.SellOfferDetail(");
strSql.Append("CompanyCD,OfferNo,SortNo,ProductID,ProductCount,UnitID,UnitPrice,TaxPrice,Discount,TaxRate,TotalFee,TotalPrice,TotalTax,SendTime,Package,Remark,ModifiedDate,ModifiedUserID,UsedUnitID,UsedUnitCount,UsedPrice,ExRate)");
strSql.Append(" values (");
strSql.Append("@CompanyCD,@OfferNo,@SortNo,@ProductID,@ProductCount,@UnitID,@UnitPrice,@TaxPrice,@Discount,@TaxRate,@TotalFee,@TotalPrice,@TotalTax,@SendTime,@Package,@Remark,getdate(),@ModifiedUserID,@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate)");
#region 参数
SqlParameter[] parameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@OfferNo", SqlDbType.VarChar,50),
new SqlParameter("@SortNo", SqlDbType.Int,4),
new SqlParameter("@ProductID", SqlDbType.Int,4),
new SqlParameter("@ProductCount", SqlDbType.Decimal,9),
new SqlParameter("@UnitID", SqlDbType.Int,4),
new SqlParameter("@UnitPrice", SqlDbType.Decimal,9),
new SqlParameter("@TaxPrice", SqlDbType.Decimal,9),
new SqlParameter("@Discount", SqlDbType.Decimal,5),
new SqlParameter("@TaxRate", SqlDbType.Decimal,5),
new SqlParameter("@TotalFee", SqlDbType.Decimal,9),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
new SqlParameter("@TotalTax", SqlDbType.Decimal,9),
new SqlParameter("@SendTime", SqlDbType.Int,4),
new SqlParameter("@Package", SqlDbType.Int,4),
new SqlParameter("@Remark", SqlDbType.VarChar,200),
new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,20),
new SqlParameter("@UsedUnitID", SqlDbType.Int,4),
new SqlParameter("@UsedUnitCount", SqlDbType.Decimal,9),
new SqlParameter("@UsedPrice", SqlDbType.Decimal,9),
new SqlParameter("@ExRate", SqlDbType.Decimal,9)};
parameters[0].Value = sellOfferDetailModel.CompanyCD;
parameters[1].Value = sellOfferDetailModel.OfferNo;
parameters[2].Value = sellOfferDetailModel.SortNo;
parameters[3].Value = sellOfferDetailModel.ProductID;
parameters[4].Value = sellOfferDetailModel.ProductCount;
parameters[5].Value = sellOfferDetailModel.UnitID;
parameters[6].Value = sellOfferDetailModel.UnitPrice;
parameters[7].Value = sellOfferDetailModel.TaxPrice;
parameters[8].Value = sellOfferDetailModel.Discount;
parameters[9].Value = sellOfferDetailModel.TaxRate;
parameters[10].Value = sellOfferDetailModel.TotalFee;
parameters[11].Value = sellOfferDetailModel.TotalPrice;
parameters[12].Value = sellOfferDetailModel.TotalTax;
parameters[13].Value = sellOfferDetailModel.SendTime;
parameters[14].Value = sellOfferDetailModel.Package;
parameters[15].Value = sellOfferDetailModel.Remark;
parameters[16].Value = sellOfferDetailModel.ModifiedUserID;
parameters[17].Value = sellOfferDetailModel.UsedUnitID;
parameters[18].Value = sellOfferDetailModel.UsedUnitCount;
parameters[19].Value = sellOfferDetailModel.UsedPrice;
parameters[20].Value = sellOfferDetailModel.ExRate;
//@UsedUnitID,@UsedUnitCount,@UsedPrice,@ExRate
foreach (SqlParameter para in parameters)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
#endregion
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters);
}
}
/// <summary>
/// 修改销售报价单主表信息
/// </summary>
/// <param name="sellOfferModel"></param>
/// <param name="tran"></param>
private static void UpdateSellOffer(SellOfferModel sellOfferModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update officedba.SellOffer set ");
strSql.Append("CustID=@CustID,");
strSql.Append("CustTel=@CustTel,");
strSql.Append("FromType=@FromType,");
strSql.Append("FromBillID=@FromBillID,");
strSql.Append("Title=@Title,");
strSql.Append("Seller=@Seller,");
strSql.Append("SellDeptId=@SellDeptId,");
strSql.Append("SellType=@SellType,");
strSql.Append("BusiType=@BusiType,");
strSql.Append("PayType=@PayType,");
strSql.Append("MoneyType=@MoneyType,");
strSql.Append("CarryType=@CarryType,");
strSql.Append("TakeType=@TakeType,");
strSql.Append("ExpireDate=@ExpireDate,");
strSql.Append("TotalPrice=@TotalPrice,");
strSql.Append("TotalTax=@TotalTax,");
strSql.Append("TotalFee=@TotalFee,");
strSql.Append("Discount=@Discount,");
strSql.Append("DiscountTotal=@DiscountTotal,");
strSql.Append("RealTotal=@RealTotal,");
strSql.Append("isAddTax=@isAddTax,");
strSql.Append("CountTotal=@CountTotal,");
strSql.Append("CurrencyType=@CurrencyType,");
strSql.Append("Rate=@Rate,");
strSql.Append("QuoteTime=@QuoteTime,");
strSql.Append("OfferDate=@OfferDate,");
strSql.Append("PayRemark=@PayRemark,");
strSql.Append("DeliverRemark=@DeliverRemark,");
strSql.Append("PackTransit=@PackTransit,");
strSql.Append("Remark=@Remark,");
strSql.Append("BillStatus=@BillStatus,");
strSql.Append("ModifiedDate=getdate(),");
strSql.Append("ModifiedUserID=@ModifiedUserID");
strSql.Append(",CanViewUser=@CanViewUser ");
strSql.Append(" where CompanyCD=@CompanyCD and OfferNo=@OfferNo ");
#region
SqlParameter[] parameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@OfferNo", SqlDbType.VarChar,50),
new SqlParameter("@CustID", SqlDbType.Int,4),
new SqlParameter("@CustTel", SqlDbType.VarChar,100),
new SqlParameter("@FromType", SqlDbType.Char,1),
new SqlParameter("@FromBillID", SqlDbType.Int,4),
new SqlParameter("@Title", SqlDbType.VarChar,100),
new SqlParameter("@Seller", SqlDbType.Int,4),
new SqlParameter("@SellDeptId", SqlDbType.Int,4),
new SqlParameter("@SellType", SqlDbType.Int,4),
new SqlParameter("@BusiType", SqlDbType.Char,1),
new SqlParameter("@PayType", SqlDbType.Int,4),
new SqlParameter("@MoneyType", SqlDbType.Int,4),
new SqlParameter("@CarryType", SqlDbType.Int,4),
new SqlParameter("@TakeType", SqlDbType.Int,4),
new SqlParameter("@ExpireDate", SqlDbType.DateTime),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
new SqlParameter("@TotalTax", SqlDbType.Decimal,9),
new SqlParameter("@TotalFee", SqlDbType.Decimal,9),
new SqlParameter("@Discount", SqlDbType.Decimal,9),
new SqlParameter("@DiscountTotal", SqlDbType.Decimal,9),
new SqlParameter("@RealTotal", SqlDbType.Decimal,9),
new SqlParameter("@isAddTax", SqlDbType.Char,1),
new SqlParameter("@CountTotal", SqlDbType.Decimal,9),
new SqlParameter("@CurrencyType", SqlDbType.Int,4),
new SqlParameter("@Rate", SqlDbType.Decimal,9),
new SqlParameter("@QuoteTime", SqlDbType.Int,4),
new SqlParameter("@OfferDate", SqlDbType.DateTime),
new SqlParameter("@PayRemark", SqlDbType.VarChar,200),
new SqlParameter("@DeliverRemark", SqlDbType.VarChar,200),
new SqlParameter("@PackTransit", SqlDbType.VarChar,200),
new SqlParameter("@Remark", SqlDbType.VarChar,1024),
new SqlParameter("@BillStatus", SqlDbType.Char,1),
new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,20),
new SqlParameter("@CanViewUser", SqlDbType.VarChar,2048)};
parameters[0].Value = sellOfferModel.CompanyCD;
parameters[1].Value = sellOfferModel.OfferNo;
parameters[2].Value = sellOfferModel.CustID;
parameters[3].Value = sellOfferModel.CustTel;
parameters[4].Value = sellOfferModel.FromType;
parameters[5].Value = sellOfferModel.FromBillID;
parameters[6].Value = sellOfferModel.Title;
parameters[7].Value = sellOfferModel.Seller;
parameters[8].Value = sellOfferModel.SellDeptId;
parameters[9].Value = sellOfferModel.SellType;
parameters[10].Value = sellOfferModel.BusiType;
parameters[11].Value = sellOfferModel.PayType;
parameters[12].Value = sellOfferModel.MoneyType;
parameters[13].Value = sellOfferModel.CarryType;
parameters[14].Value = sellOfferModel.TakeType;
parameters[15].Value = sellOfferModel.ExpireDate;
parameters[16].Value = sellOfferModel.TotalPrice;
parameters[17].Value = sellOfferModel.TotalTax;
parameters[18].Value = sellOfferModel.TotalFee;
parameters[19].Value = sellOfferModel.Discount;
parameters[20].Value = sellOfferModel.DiscountTotal;
parameters[21].Value = sellOfferModel.RealTotal;
parameters[22].Value = sellOfferModel.isAddTax;
parameters[23].Value = sellOfferModel.CountTotal;
parameters[24].Value = sellOfferModel.CurrencyType;
parameters[25].Value = sellOfferModel.Rate;
parameters[26].Value = sellOfferModel.QuoteTime;
parameters[27].Value = sellOfferModel.OfferDate;
parameters[28].Value = sellOfferModel.PayRemark;
parameters[29].Value = sellOfferModel.DeliverRemark;
parameters[30].Value = sellOfferModel.PackTransit;
parameters[31].Value = sellOfferModel.Remark;
parameters[32].Value = sellOfferModel.BillStatus;
parameters[33].Value = sellOfferModel.ModifiedUserID;
parameters[34].Value = sellOfferModel.CanViewUser;
foreach (SqlParameter para in parameters)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
#endregion
//如果单据状态变为变更状态，回写订单中已执行数量
if (sellOfferModel.BillStatus == "3")
{
SqlParameter[] p = { new SqlParameter("@CompanyCD", sellOfferModel.CompanyCD), new SqlParameter("@OfferNo", sellOfferModel.OfferNo) };
string str = SqlHelper.ExecuteScalar("select BillStatus from officedba.SellOffer where CompanyCD=@CompanyCD and OfferNo=@OfferNo ", p).ToString();
if (str == "2")
{
UpdateSellChance(2, sellOfferModel.OfferNo, tran);
}
}
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters);
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(SellOfferModel model, Hashtable htExtAttr, TransactionManager tran)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.SellOffer set ";
SqlParameter[] parameters = new SqlParameter[htExtAttr.Count + 2];
int i = 0;
foreach (DictionaryEntry de in htExtAttr)// de为一个Hashtable实例
{
strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ",";
parameters[i] = SqlHelper.GetParameter("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim());
i++;
}
int iLength = strSql.Length - 1;
strSql = strSql.Substring(0, iLength);
strSql += " where CompanyCD = @CompanyCD AND OfferNo = @OfferNo";
parameters[i] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD);
parameters[i + 1] = SqlHelper.GetParameter("@OfferNo", model.OfferNo);
//cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
//cmd.Parameters.AddWithValue("@PlanNo", model.PlanNo);
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters);
//cmd.CommandText = strSql;
}
catch (Exception ex)
{
string ss = ex.Message;
}
}
/// <summary>
/// 添加销售报价单主表信息
/// </summary>
/// <param name="sellOfferModel"></param>
private static void InsertSellOffer(SellOfferModel sellOfferModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.SellOffer(");
strSql.Append("CompanyCD,OfferNo,CustID,CustTel,FromType,FromBillID,Title,Seller,SellDeptId,SellType,BusiType,PayType,MoneyType,CarryType,TakeType,ExpireDate,TotalPrice,TotalTax,TotalFee,Discount,DiscountTotal,RealTotal,isAddTax,CountTotal,CurrencyType,Rate,QuoteTime,OfferDate,PayRemark,DeliverRemark,PackTransit,Remark,BillStatus,Creator,CreateDate,ModifiedDate,ModifiedUserID,CanViewUser)");
strSql.Append(" values (");
strSql.Append("@CompanyCD,@OfferNo,@CustID,@CustTel,@FromType,@FromBillID,@Title,@Seller,@SellDeptId,@SellType,@BusiType,@PayType,@MoneyType,@CarryType,@TakeType,@ExpireDate,@TotalPrice,@TotalTax,@TotalFee,@Discount,@DiscountTotal,@RealTotal,@isAddTax,@CountTotal,@CurrencyType,@Rate,@QuoteTime,@OfferDate,@PayRemark,@DeliverRemark,@PackTransit,@Remark,@BillStatus,@Creator,getdate(),getdate(),@ModifiedUserID,@CanViewUser)");
#region 参数
SqlParameter[] parameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@OfferNo", SqlDbType.VarChar,50),
new SqlParameter("@CustID", SqlDbType.Int,4),
new SqlParameter("@CustTel", SqlDbType.VarChar,100),
new SqlParameter("@FromType", SqlDbType.Char,1),
new SqlParameter("@FromBillID", SqlDbType.Int,4),
new SqlParameter("@Title", SqlDbType.VarChar,100),
new SqlParameter("@Seller", SqlDbType.Int,4),
new SqlParameter("@SellDeptId", SqlDbType.Int,4),
new SqlParameter("@SellType", SqlDbType.Int,4),
new SqlParameter("@BusiType", SqlDbType.Char,1),
new SqlParameter("@PayType", SqlDbType.Int,4),
new SqlParameter("@MoneyType", SqlDbType.Int,4),
new SqlParameter("@CarryType", SqlDbType.Int,4),
new SqlParameter("@TakeType", SqlDbType.Int,4),
new SqlParameter("@ExpireDate", SqlDbType.DateTime),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
new SqlParameter("@TotalTax", SqlDbType.Decimal,9),
new SqlParameter("@TotalFee", SqlDbType.Decimal,9),
new SqlParameter("@Discount", SqlDbType.Decimal,9),
new SqlParameter("@DiscountTotal", SqlDbType.Decimal,9),
new SqlParameter("@RealTotal", SqlDbType.Decimal,9),
new SqlParameter("@isAddTax", SqlDbType.Char,1),
new SqlParameter("@CountTotal", SqlDbType.Decimal,9),
new SqlParameter("@CurrencyType", SqlDbType.Int,4),
new SqlParameter("@Rate", SqlDbType.Decimal,9),
new SqlParameter("@QuoteTime", SqlDbType.Int,4),
new SqlParameter("@OfferDate", SqlDbType.DateTime),
new SqlParameter("@PayRemark", SqlDbType.VarChar,200),
new SqlParameter("@DeliverRemark", SqlDbType.VarChar,200),
new SqlParameter("@PackTransit", SqlDbType.VarChar,200),
new SqlParameter("@Remark", SqlDbType.VarChar,1024),
new SqlParameter("@BillStatus", SqlDbType.Char,1),
new SqlParameter("@Creator", SqlDbType.Int,4),
new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,20),
new SqlParameter("@CanViewUser", SqlDbType.VarChar,2048)};
parameters[0].Value = sellOfferModel.CompanyCD;
parameters[1].Value = sellOfferModel.OfferNo;
parameters[2].Value = sellOfferModel.CustID;
parameters[3].Value = sellOfferModel.CustTel;
parameters[4].Value = sellOfferModel.FromType;
parameters[5].Value = sellOfferModel.FromBillID;
parameters[6].Value = sellOfferModel.Title;
parameters[7].Value = sellOfferModel.Seller;
parameters[8].Value = sellOfferModel.SellDeptId;
parameters[9].Value = sellOfferModel.SellType;
parameters[10].Value = sellOfferModel.BusiType;
parameters[11].Value = sellOfferModel.PayType;
parameters[12].Value = sellOfferModel.MoneyType;
parameters[13].Value = sellOfferModel.CarryType;
parameters[14].Value = sellOfferModel.TakeType;
parameters[15].Value = sellOfferModel.ExpireDate;
parameters[16].Value = sellOfferModel.TotalPrice;
parameters[17].Value = sellOfferModel.TotalTax;
parameters[18].Value = sellOfferModel.TotalFee;
parameters[19].Value = sellOfferModel.Discount;
parameters[20].Value = sellOfferModel.DiscountTotal;
parameters[21].Value = sellOfferModel.RealTotal;
parameters[22].Value = sellOfferModel.isAddTax;
parameters[23].Value = sellOfferModel.CountTotal;
parameters[24].Value = sellOfferModel.CurrencyType;
parameters[25].Value = sellOfferModel.Rate;
parameters[26].Value = sellOfferModel.QuoteTime;
parameters[27].Value = sellOfferModel.OfferDate;
parameters[28].Value = sellOfferModel.PayRemark;
parameters[29].Value = sellOfferModel.DeliverRemark;
parameters[30].Value = sellOfferModel.PackTransit;
parameters[31].Value = sellOfferModel.Remark;
parameters[32].Value = sellOfferModel.BillStatus;
parameters[33].Value = sellOfferModel.Creator;
parameters[34].Value = sellOfferModel.ModifiedUserID;
parameters[35].Value = sellOfferModel.CanViewUser;
foreach (SqlParameter para in parameters)
{
if (para.Value == null)
{
para.Value = DBNull.Value;
}
}
#endregion
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSql.ToString(), parameters);
}
/// <summary>
/// 获取报价单列表
/// </summary>
/// <param name="sellOfferModel">sellOfferModel表实体</param>
/// <param name="FlowStatus">审批状态</param>
/// <returns></returns>
public static DataTable GetOrderList(string EFIndex, string EFDesc, SellOfferModel sellOfferModel, int? FlowStatus, DateTime? dt, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return SellOfferDBHelper.GetOrderList(EFIndex, EFDesc,sellOfferModel, FlowStatus, dt, pageIndex, pageCount, ord, ref TotalCount);
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
//设置行为参数
string orderString = hiddExpOrder.Value.Trim();//排序
string order = "desc";//排序：降序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "OfferNo";//要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_a"))
{
order = "asc";//排序：升序
}
int pageCount = int.Parse(hiddExpTotal.Value);//每页显示记录数
int pageIndex = 1;//当前页
int TotalCount = 0;//总记录数
string ord = orderBy + " " + order;//排序字段
DataTable dt = new DataTable();
string strOfferNo = hiddExpOrderNo.Value.Trim();
string strTitle = hiddExpTitle.Value.Trim();
string strCustID = hiddExpCustID.Value.Trim();
string strSeller = hiddExpSeller.Value.Trim();
string strFromType = hiddExpFromType.Value.Trim();
string strBillStatus = hiddExpBillStatus.Value.Trim();
string strOfferDate = hiddExpOfferDate.Value.Trim();
string strOfferDate1 = hiddExpOfferDate1.Value.Trim();
string strFlowStatus = hiddExpFlowStatus.Value.Trim();
string strFromBillID = hiddExpFromBillID.Value.Trim();
string OfferNo = strOfferNo.Length == 0 ? null : strOfferNo;
string Title = strTitle.Length == 0 ? null : strTitle;
int? CustID = strCustID.Length == 0 ? null : (int?)Convert.ToInt32(strCustID);
int? Seller = strSeller.Length == 0 ? null : (int?)Convert.ToInt32(strSeller);
string FromType = strFromType.Length == 0 ? null : strFromType;
string BillStatus = strBillStatus.Length == 0 ? null : strBillStatus;
DateTime? OfferDate = strOfferDate.Length == 0 ? null : (DateTime?)Convert.ToDateTime(strOfferDate);
DateTime? OfferDate1 = strOfferDate1.Length == 0 ? null : (DateTime?)Convert.ToDateTime(strOfferDate1);
int? FlowStatus = strFlowStatus.Length == 0 ? null : (int?)Convert.ToInt32(strFlowStatus);
int? FromBillID = strFromBillID.Length == 0 ? null : (int?)Convert.ToInt32(strFromBillID);
SellOfferModel model = new SellOfferModel();
model.BillStatus = BillStatus;
model.CustID = CustID;
model.FromBillID = FromBillID;
model.FromType = FromType;
model.OfferDate = OfferDate;
model.OfferNo = OfferNo;
model.Seller = Seller;
model.Title = Title;
model.Creator = UserInfo.EmployeeID;//临时存储当前登录人ID
dt = SellOfferBus.GetOrderList(GetBillExAttrControl1.GetExtIndexValue, GetBillExAttrControl1.GetExtTxtValue, model, FlowStatus, OfferDate1, pageIndex, pageCount, ord, ref TotalCount);
//导出标题
string headerTitle = "报价单号|报价单主题|源单类型|销售机会|业务员|报价日期|报价次数|金额|单据状态|审批状态";
//string headerTitle = "建档日期|启用状态";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "OfferNo|Title|FromTypeText|ChanceNo|EmployeeName|OfferDate|QuoteTime|TotalFee|BillStatusText|FlowInstanceText";
//string columnFiled = "CreateDate|strUsedStatus";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "销售报价单列表");
}
