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) : "ContractNo";//要排序的字段，如果为空，默认为"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 strorderNo = 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 strFlowStatus = hiddExpFlowStatus.Value.Trim();
string strFromBillID = hiddExpFromBillID.Value.Trim();
string orderNo = strorderNo.Length == 0 ? null : strorderNo;
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;
int? FlowStatus = strFlowStatus.Length == 0 ? null : (int?)Convert.ToInt32(strFlowStatus);
int? FromBillID = strFromBillID.Length == 0 ? null : (int?)Convert.ToInt32(strFromBillID);
SellContractModel model = new SellContractModel();
model.BillStatus = BillStatus;
model.CustID = CustID;
model.FromBillID = FromBillID;
model.FromType = FromType;
model.ContractNo = orderNo;
model.Seller = Seller;
model.Title = Title;
model.Creator = UserInfo.EmployeeID;
dt = SellContractBus.GetOrderList(GetBillExAttrControl1.GetExtIndexValue, GetBillExAttrControl1.GetExtTxtValue, model, FlowStatus, pageIndex, pageCount, ord, ref TotalCount);
//导出标题
string headerTitle = "合同编号|合同主题|源单类型|销售报价单|业务员|客户|合同金额|合同状态|状态|审批状态";
//string headerTitle = "建档日期|启用状态";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "ContractNo|Title|FromTypeText|OfferNo|EmployeeName|CustName|TotalFee|stateText|BillStatusText|FlowInstanceText";
//string columnFiled = "CreateDate|strUsedStatus";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "销售合同列表");
}
/// <summary>
/// 添加销售合同
/// </summary>
/// <param name="sellChanceModel"></param>
/// <param name="sellChancePushModel"></param>
/// <returns>是否添加成功</returns>
public static bool InsertOrder(Hashtable ht,SellContractModel sellContractModel,
List<SellContractDetailModel> SellContractDetailModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
//判断单据编号是否存在
if (NoIsExist(sellContractModel.ContractNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
InsertSellContract(sellContractModel, tran);
//拓展属性
GetExtAttrCmd(sellContractModel, ht, tran);
InsertSellContractDetail(SellContractDetailModelList, sellContractModel, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
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,SellContractModel sellContractModel,
List<SellContractDetailModel> SellContractDetailModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
if (IsUpdate(sellContractModel.ContractNo))
{
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
UpdateSellContract(sellContractModel, tran);
//拓展属性
GetExtAttrCmd(sellContractModel, ht, tran);
InsertSellContractDetail(SellContractDetailModelList, sellContractModel, tran);
tran.Commit();
isSucc = true;
strMsg = "保存成功！";
}
catch (Exception ex)
{
tran.Rollback();
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,SellContractModel sellContractModel,
List<SellContractDetailModel> SellContractDetailModelList, out string strMsg)
{
bool isSucc = false;//是否添加成功
//定义变量
string remark = string.Empty;
strMsg = "";
try
{
isSucc = SellContractDBHelper.UpdateOrder(ht,sellContractModel, SellContractDetailModelList, out strMsg);
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
catch (Exception ex)
{
//输出日志
SellLogCommon.WriteSystemLog(ex, LogInfo.LogType.SYSTEM, LogInfo.SystemLogKind.SYSTEM_ERROR, ConstUtil.MODULE_ID_SELLCONTRANCT_ADD);
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
SellLogCommon.InsertLog(sellContractModel.ContractNo, ConstUtil.MODULE_ID_SELLCONTRANCT_ADD, ConstUtil.CODING_RULE_TABLE_SELLCONTRANCT, remark, ConstUtil.LOG_PROCESS_UPDATE);
return isSucc;
}
/// <summary>
/// 获取合同列表
/// </summary>
/// <param name="sellContractModel">sellContractModel表实体</param>
/// <param name="FlowStatus">审批状态</param>
/// <returns></returns>
public static DataTable GetOrderList(string EFIndex,string EFDesc,SellContractModel sellContractModel, int? FlowStatus, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
string strSql = string.Empty;
strSql = "select * from ( SELECT s.ID, s.Title,s.ModifiedDate, s.ContractNo, s.TotalFee,case s.FromType when ";
strSql += " '1' then ISNULL(so.OfferNo, '') when '2' then isnull(sc.ChanceNo,'') end as OfferNo, ";
strSql += "ISNULL(e.EmployeeName, '') AS EmployeeName,isnull( c.CustName,'') as CustName, ";
strSql += "CASE s.FromType WHEN '0' THEN '无来源' WHEN '1' THEN '销售报价单' WHEN '2' THEN '销售机会' END AS FromTypeText, ";
strSql += "CASE s.BillStatus WHEN 1 THEN '制单' WHEN 2 THEN '执行' WHEN 3 THEN '变更' ";
strSql += "WHEN 4 THEN '手工结单' WHEN 5 THEN '自动结单' END AS BillStatusText, ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 2 ";
strSql += "ORDER BY ModifiedDate DESC) AS FlowStatus , ";
strSql += "CASE WHEN (SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 2 ";
strSql += "ORDER BY ModifiedDate DESC) IS NULL THEN '' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 2 ";
strSql += "ORDER BY ModifiedDate DESC) = 1 THEN '待审批' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 2 ";
strSql += "ORDER BY ModifiedDate DESC) = 2 THEN '审批中' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 2 ";
strSql += "ORDER BY ModifiedDate DESC) = 3 THEN '审批通过' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 2 ";
strSql += "ORDER BY ModifiedDate DESC) = 4 THEN '审批不通过' WHEN ";
strSql += "(SELECT TOP 1 FlowStatus ";
strSql += "FROM officedba.FlowInstance ";
strSql += "WHERE BillID = s.ID AND BillTypeFlag = 5 AND BillTypeCode = 2 ";
strSql += "ORDER BY ModifiedDate DESC) = 5 THEN '撤销审批' END AS FlowInstanceText, ";
strSql += "CASE s.State WHEN '1' THEN '执行中' WHEN '2' THEN '意外终止' WHEN '3' ";
strSql += "THEN '已执行' when '4' then '已到期' END AS stateText, isnull(CASE ";
strSql += "(SELECT count(1) FROM officedba.SellOrder AS sod ";
strSql += "WHERE sod.FromType = '2' AND sod.FromBillID = s.ID) WHEN 0 THEN '无引用' END, '被引用') AS RefText ";
strSql += "FROM officedba.SellContract AS s LEFT OUTER JOIN ";
strSql += "officedba.SellOffer AS so ON s.FromBillID = so.ID and s.FromType='1' LEFT OUTER JOIN ";
strSql += "officedba.SellChance AS sc ON s.FromBillID = sc.ID and s.FromType='2' LEFT OUTER JOIN ";
strSql += "officedba.EmployeeInfo AS e ON s.Seller = e.ID LEFT OUTER JOIN ";
strSql += "officedba.CustInfo AS c ON s.CustID = c.ID where 1=1 and s.CompanyCD=@CompanyCD ";
strSql += " and ( charindex('," + sellContractModel.Creator + ",' , ','+s.CanViewUser+',')>0 or s.Creator=" + sellContractModel.Creator + " OR s.CanViewUser='' OR s.CanViewUser is null) ";
string strCompanyCD = string.Empty;//单位编号
strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
ArrayList arr = new ArrayList();
arr.Add(new SqlParameter("@CompanyCD", strCompanyCD));
if (sellContractModel.BillStatus != null)
{
strSql += " and s.BillStatus= @BillStatus";
arr.Add(new SqlParameter("@BillStatus", sellContractModel.BillStatus));
}
if (sellContractModel.FromBillID != null)
{
strSql += " and s.FromBillID=@FromBillID";
arr.Add(new SqlParameter("@FromBillID", sellContractModel.FromBillID)); ;
}
if (sellContractModel.FromType != null)
{
strSql += " and s.FromType=@FromType";
arr.Add(new SqlParameter("@FromType", sellContractModel.FromType));
}
if (sellContractModel.ContractNo != null)
{
strSql += " and s.ContractNo like @ContractNo";
arr.Add(new SqlParameter("@ContractNo", "%" + sellContractModel.ContractNo + "%"));
}
if (sellContractModel.Seller != null)
{
strSql += " and s.Seller=@Seller";
arr.Add(new SqlParameter("@Seller", sellContractModel.Seller));
}
if (sellContractModel.Title != null)
{
strSql += " and s.Title like @Title";
arr.Add(new SqlParameter("@Title", "%" + sellContractModel.Title + "%"));
}
if (sellContractModel.CustID != null)
{
strSql += " and s.CustID=@CustID";
arr.Add(new SqlParameter("@CustID", sellContractModel.CustID));
}
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
strSql += " and s.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="sellContractModel"></param>
private static void InsertSellContractDetail(List<SellContractDetailModel> SellContractDetailModelList, SellContractModel sellContractModel, TransactionManager tran)
{
string strSqlDel = "delete from officedba.SellContractDetail where ContractNo=@ContractNo and CompanyCD=@CompanyCD ";
SqlParameter[] paras = { new SqlParameter("@ContractNo", sellContractModel.ContractNo), new SqlParameter("@CompanyCD", sellContractModel.CompanyCD) };
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSqlDel.ToString(), paras);
foreach (SellContractDetailModel sellOfferDetailModel in SellContractDetailModelList)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.SellContractDetail(");
strSql.Append("CompanyCD,ContractNo,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,@ContractNo,@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("@ContractNo", 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.ContractNo;
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;
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>
/// <returns></returns>
private static void GetExtAttrCmd(SellContractModel model, Hashtable htExtAttr, TransactionManager tran)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.SellContract 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 ContractNo = @ContractNo";
parameters[i] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD);
parameters[i + 1] = SqlHelper.GetParameter("@ContractNo", model.ContractNo);
//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="sellContractModel"></param>
/// <param name="tran"></param>
private static void UpdateSellContract(SellContractModel sellContractModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update officedba.SellContract set ");
strSql.Append("FromType=@FromType,");
strSql.Append("FromBillID=@FromBillID,");
strSql.Append("CustID=@CustID,");
strSql.Append("CustTel=@CustTel,");
strSql.Append("Title=@Title,");
strSql.Append("SellType=@SellType,");
strSql.Append("BusiType=@BusiType,");
strSql.Append("CurrencyType=@CurrencyType,");
strSql.Append("Rate=@Rate,");
strSql.Append("TotalPrice=@TotalPrice,");
strSql.Append("Tax=@Tax,");
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("PayType=@PayType,");
strSql.Append("MoneyType=@MoneyType,");
strSql.Append("CarryType=@CarryType,");
strSql.Append("TakeType=@TakeType,");
strSql.Append("Seller=@Seller,");
strSql.Append("SellDeptId=@SellDeptId,");
strSql.Append("SignDate=@SignDate,");
strSql.Append("StartDate=@StartDate,");
strSql.Append("EndDate=@EndDate,");
strSql.Append("SignAddr=@SignAddr,");
strSql.Append("TheyDelegate=@TheyDelegate,");
strSql.Append("OurDelegate=@OurDelegate,");
strSql.Append("State=@State,");
strSql.Append("EndNote=@EndNote,");
strSql.Append("TalkProcess=@TalkProcess,");
strSql.Append("Remark=@Remark,");
strSql.Append("Attachment=@Attachment,");
strSql.Append("BillStatus=@BillStatus,");
strSql.Append("ModifiedDate=getdate(),");
strSql.Append("ModifiedUserID=@ModifiedUserID");
strSql.Append(",CanViewUser=@CanViewUser");
strSql.Append(" where CompanyCD=@CompanyCD and ContractNo=@ContractNo");
#region
SqlParameter[] parameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@FromType", SqlDbType.Char,1),
new SqlParameter("@FromBillID", SqlDbType.Int,4),
new SqlParameter("@CustID", SqlDbType.Int,4),
new SqlParameter("@CustTel", SqlDbType.VarChar,100),
new SqlParameter("@Title", SqlDbType.VarChar,100),
new SqlParameter("@ContractNo", SqlDbType.VarChar,50),
new SqlParameter("@SellType", SqlDbType.Int,4),
new SqlParameter("@BusiType", SqlDbType.Char,1),
new SqlParameter("@CurrencyType", SqlDbType.Int,4),
new SqlParameter("@Rate", SqlDbType.Decimal,9),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
new SqlParameter("@Tax", 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("@PayType", SqlDbType.Int,4),
new SqlParameter("@MoneyType", SqlDbType.Int,4),
new SqlParameter("@CarryType", SqlDbType.Int,4),
new SqlParameter("@TakeType", SqlDbType.Int,4),
new SqlParameter("@Seller", SqlDbType.Int,4),
new SqlParameter("@SellDeptId", SqlDbType.Int,4),
new SqlParameter("@SignDate", SqlDbType.DateTime),
new SqlParameter("@StartDate", SqlDbType.DateTime),
new SqlParameter("@EndDate", SqlDbType.DateTime),
new SqlParameter("@SignAddr", SqlDbType.VarChar,100),
new SqlParameter("@TheyDelegate", SqlDbType.VarChar,50),
new SqlParameter("@OurDelegate", SqlDbType.Int,4),
new SqlParameter("@State", SqlDbType.Char,1),
new SqlParameter("@EndNote", SqlDbType.VarChar,200),
new SqlParameter("@TalkProcess", SqlDbType.VarChar,200),
new SqlParameter("@Remark", SqlDbType.VarChar,200),
new SqlParameter("@Attachment", SqlDbType.VarChar,150),
new SqlParameter("@BillStatus", SqlDbType.Char,1),
new SqlParameter("@ModifiedUserID", SqlDbType.VarChar,20),
new SqlParameter("@CanViewUser", SqlDbType.VarChar,2048)};
parameters[0].Value = sellContractModel.CompanyCD;
parameters[1].Value = sellContractModel.FromType;
parameters[2].Value = sellContractModel.FromBillID;
parameters[3].Value = sellContractModel.CustID;
parameters[4].Value = sellContractModel.CustTel;
parameters[5].Value = sellContractModel.Title;
parameters[6].Value = sellContractModel.ContractNo;
parameters[7].Value = sellContractModel.SellType;
parameters[8].Value = sellContractModel.BusiType;
parameters[9].Value = sellContractModel.CurrencyType;
parameters[10].Value = sellContractModel.Rate;
parameters[11].Value = sellContractModel.TotalPrice;
parameters[12].Value = sellContractModel.Tax;
parameters[13].Value = sellContractModel.TotalFee;
parameters[14].Value = sellContractModel.Discount;
parameters[15].Value = sellContractModel.DiscountTotal;
parameters[16].Value = sellContractModel.RealTotal;
parameters[17].Value = sellContractModel.isAddTax;
parameters[18].Value = sellContractModel.CountTotal;
parameters[19].Value = sellContractModel.PayType;
parameters[20].Value = sellContractModel.MoneyType;
parameters[21].Value = sellContractModel.CarryType;
parameters[22].Value = sellContractModel.TakeType;
parameters[23].Value = sellContractModel.Seller;
parameters[24].Value = sellContractModel.SellDeptId;
parameters[25].Value = sellContractModel.SignDate;
parameters[26].Value = sellContractModel.StartDate;
parameters[27].Value = sellContractModel.EndDate;
parameters[28].Value = sellContractModel.SignAddr;
parameters[29].Value = sellContractModel.TheyDelegate;
parameters[30].Value = sellContractModel.OurDelegate;
parameters[31].Value = sellContractModel.State;
parameters[32].Value = sellContractModel.EndNote;
parameters[33].Value = sellContractModel.TalkProcess;
parameters[34].Value = sellContractModel.Remark;
parameters[35].Value = sellContractModel.Attachment;
parameters[36].Value = sellContractModel.BillStatus;
parameters[37].Value = sellContractModel.ModifiedUserID;
parameters[38].Value = sellContractModel.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="sellContractModel"></param>
private static void InsertSellContract(SellContractModel sellContractModel, TransactionManager tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.SellContract(");
strSql.Append("CompanyCD,FromType,FromBillID,CustID,CustTel,Title,ContractNo,SellType,BusiType,CurrencyType,Rate,TotalPrice,Tax,TotalFee,Discount,DiscountTotal,RealTotal,isAddTax,CountTotal,PayType,MoneyType,CarryType,TakeType,Seller,SellDeptId,SignDate,StartDate,EndDate,SignAddr,TheyDelegate,OurDelegate,State,EndNote,TalkProcess,Remark,Attachment,BillStatus,Creator,CreateDate,ModifiedDate,ModifiedUserID,CanViewUser)");
strSql.Append(" values (");
strSql.Append("@CompanyCD,@FromType,@FromBillID,@CustID,@CustTel,@Title,@ContractNo,@SellType,@BusiType,@CurrencyType,@Rate,@TotalPrice,@Tax,@TotalFee,@Discount,@DiscountTotal,@RealTotal,@isAddTax,@CountTotal,@PayType,@MoneyType,@CarryType,@TakeType,@Seller,@SellDeptId,@SignDate,@StartDate,@EndDate,@SignAddr,@TheyDelegate,@OurDelegate,@State,@EndNote,@TalkProcess,@Remark,@Attachment,@BillStatus,@Creator,getdate(),getdate(),@ModifiedUserID,@CanViewUser)");
#region 参数
SqlParameter[] parameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@FromType", SqlDbType.Char,1),
new SqlParameter("@FromBillID", SqlDbType.Int,4),
new SqlParameter("@CustID", SqlDbType.Int,4),
new SqlParameter("@CustTel", SqlDbType.VarChar,100),
new SqlParameter("@Title", SqlDbType.VarChar,100),
new SqlParameter("@ContractNo", SqlDbType.VarChar,50),
new SqlParameter("@SellType", SqlDbType.Int,4),
new SqlParameter("@BusiType", SqlDbType.Char,1),
new SqlParameter("@CurrencyType", SqlDbType.Int,4),
new SqlParameter("@Rate", SqlDbType.Decimal,9),
new SqlParameter("@TotalPrice", SqlDbType.Decimal,9),
new SqlParameter("@Tax", 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("@PayType", SqlDbType.Int,4),
new SqlParameter("@MoneyType", SqlDbType.Int,4),
new SqlParameter("@CarryType", SqlDbType.Int,4),
new SqlParameter("@TakeType", SqlDbType.Int,4),
new SqlParameter("@Seller", SqlDbType.Int,4),
new SqlParameter("@SellDeptId", SqlDbType.Int,4),
new SqlParameter("@SignDate", SqlDbType.DateTime),
new SqlParameter("@StartDate", SqlDbType.DateTime),
new SqlParameter("@EndDate", SqlDbType.DateTime),
new SqlParameter("@SignAddr", SqlDbType.VarChar,100),
new SqlParameter("@TheyDelegate", SqlDbType.VarChar,50),
new SqlParameter("@OurDelegate", SqlDbType.Int,4),
new SqlParameter("@State", SqlDbType.Char,1),
new SqlParameter("@EndNote", SqlDbType.VarChar,200),
new SqlParameter("@TalkProcess", SqlDbType.VarChar,200),
new SqlParameter("@Remark", SqlDbType.VarChar,200),
new SqlParameter("@Attachment", SqlDbType.VarChar,150),
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 = sellContractModel.CompanyCD;
parameters[1].Value = sellContractModel.FromType;
parameters[2].Value = sellContractModel.FromBillID;
parameters[3].Value = sellContractModel.CustID;
parameters[4].Value = sellContractModel.CustTel;
parameters[5].Value = sellContractModel.Title;
parameters[6].Value = sellContractModel.ContractNo;
parameters[7].Value = sellContractModel.SellType;
parameters[8].Value = sellContractModel.BusiType;
parameters[9].Value = sellContractModel.CurrencyType;
parameters[10].Value = sellContractModel.Rate;
parameters[11].Value = sellContractModel.TotalPrice;
parameters[12].Value = sellContractModel.Tax;
parameters[13].Value = sellContractModel.TotalFee;
parameters[14].Value = sellContractModel.Discount;
parameters[15].Value = sellContractModel.DiscountTotal;
parameters[16].Value = sellContractModel.RealTotal;
parameters[17].Value = sellContractModel.isAddTax;
parameters[18].Value = sellContractModel.CountTotal;
parameters[19].Value = sellContractModel.PayType;
parameters[20].Value = sellContractModel.MoneyType;
parameters[21].Value = sellContractModel.CarryType;
parameters[22].Value = sellContractModel.TakeType;
parameters[23].Value = sellContractModel.Seller;
parameters[24].Value = sellContractModel.SellDeptId;
parameters[25].Value = sellContractModel.SignDate;
parameters[26].Value = sellContractModel.StartDate;
parameters[27].Value = sellContractModel.EndDate;
parameters[28].Value = sellContractModel.SignAddr;
parameters[29].Value = sellContractModel.TheyDelegate;
parameters[30].Value = sellContractModel.OurDelegate;
parameters[31].Value = sellContractModel.State;
parameters[32].Value = sellContractModel.EndNote;
parameters[33].Value = sellContractModel.TalkProcess;
parameters[34].Value = sellContractModel.Remark;
parameters[35].Value = sellContractModel.Attachment;
parameters[36].Value = sellContractModel.BillStatus;
parameters[37].Value = sellContractModel.Creator;
parameters[38].Value = sellContractModel.ModifiedUserID;
parameters[39].Value = sellContractModel.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,SellContractModel sellContractModel, int? FlowStatus, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return SellContractDBHelper.GetOrderList(EFIndex, EFDesc, sellContractModel, FlowStatus, pageIndex, pageCount, ord, ref TotalCount);
}
