protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
PurchaseOrderModel PurchaseOrderM = new PurchaseOrderModel();
PurchaseOrderM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
PurchaseOrderM.OrderNo = txtOrderNo.Value;
PurchaseOrderM.Title = txtOrderTitle.Value;
PurchaseOrderM.TypeID = ddlTypeID.SelectedValue;
PurchaseOrderM.DeptID = hidDeptID.Value;
PurchaseOrderM.Purchaser = hidPurchaseID.Value;
PurchaseOrderM.FromType = ddlFromType.Value;
PurchaseOrderM.ProviderID = hidProviderID.Value;
PurchaseOrderM.BillStatus = ddlBillStatus.Value;
PurchaseOrderM.FlowStatus = ddlFlowStatus.Value;
PurchaseOrderM.ProjectID = this.hidProjectID .Value;
PurchaseOrderM.EFIndex = GetBillExAttrControl1.GetExtIndexValue ;
PurchaseOrderM.EFDesc = GetBillExAttrControl1.GetExtTxtValue;
string OrderBy = hidOrderBy.Value;
DataTable dt = PurchaseOrderBus.GetPurchaseOrder(PurchaseOrderM, OrderBy);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "订单编号", "订单主题", "采购类别", "采购员", "供应商", "金额合计", "税额合计", "含税金额合计", "单据状态", "审批状态", "是否已建单" },
new string[] { "OrderNo", "OrderTitle", "TypeName", "PurchaserName", "ProviderName", "TotalPrice", "TotalTax", "TotalFee", "BillStatusName", "FlowStatusName", "isOpenBillName" },
"采购订单列表");
}
public static DataTable GetPurchaseOrder(PurchaseOrderModel PurchaseOrderM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
SqlCommand comm = new SqlCommand();
#region SQL文
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT A.ID ");
sql.AppendLine(" , A.CompanyCD ");
sql.AppendLine(" , A.OrderNo ");
sql.AppendLine(" , A.Title AS OrderTitle ");
sql.AppendLine(" , A.TypeID ");
sql.AppendLine(" , E.TypeName ");
sql.AppendLine(" , A.Purchaser as PurchaserID ");
sql.AppendLine(" , B.EmployeeName AS PurchaserName ");
sql.AppendLine(" , A.ProviderID ");
sql.AppendLine(" , C.CustName AS ProviderName ");
sql.AppendLine(" , A.TotalPrice ");
sql.AppendLine(" , A.TotalTax ");
sql.AppendLine(" , A.TotalFee ");
sql.AppendLine(" , A.isOpenBill ");
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 '自动结单' end AS BillStatusName ");
sql.AppendLine(" , D.FlowStatus ");
sql.AppendLine(" ,case D.FlowStatus when '1' then '待审批' when '2' then '审批中' when '3' ");
sql.AppendLine(" then '审批通过' when '4' then '审批不通过' when '5' then '撤销审批' else '' end as FlowStatusName ,isnull( A.ModifiedDate,'') AS ModifiedDate ");
sql.AppendLine(" FROM officedba.PurchaseOrder AS A ");
sql.AppendLine(" left JOIN officedba.EmployeeInfo AS B ON A.Purchaser = B.ID ");
sql.AppendLine(" left JOIN officedba.ProviderInfo AS C ON A.ProviderID = C.ID ");
sql.AppendLine(" LEFT JOIN officedba.FlowInstance AS D ON A.ID = D.BillID AND D.BillTypeFlag = 6 AND D.BillTypeCode = 4 ");
sql.AppendLine(" AND D.ID=(SELECT max(ID) FROM officedba.FlowInstance AS F WHERE A.ID = F.BillID AND F.BillTypeFlag = 6 AND F.BillTypeCode = 4 )");
sql.AppendLine(" LEFT JOIN officedba.CodePublicType AS E ON A.TypeID=E.ID ");
sql.AppendLine(" WHERE 1=1");
sql.AppendLine(" AND A.CompanyCD=@CompanyCD");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", PurchaseOrderM.CompanyCD));
if (PurchaseOrderM.OrderNo != "")
{
sql.AppendLine(" AND A.OrderNo like @OrderNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OrderNo", "%" + PurchaseOrderM.OrderNo + "%"));
}
if (PurchaseOrderM.Title != "")
{
sql.AppendLine(" AND A.Title like @Title");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", "%" + PurchaseOrderM.Title + "%"));
}
if (PurchaseOrderM.TypeID != "")
{
sql.AppendLine(" AND A.TypeID = @TypeID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", PurchaseOrderM.TypeID));
}
if (PurchaseOrderM.DeptID != "")
{
sql.AppendLine(" AND A.DeptID = @DeptID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", PurchaseOrderM.DeptID));
}
if (PurchaseOrderM.Purchaser != "")
{
sql.AppendLine(" AND A.Purchaser = @Purchaser");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Purchaser", PurchaseOrderM.Purchaser));
}
if (PurchaseOrderM.FromType != "10")
{
sql.AppendLine(" AND A.FromType = @FromType");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", PurchaseOrderM.FromType));
}
if (PurchaseOrderM.ProviderID != "")
{
sql.AppendLine(" AND A.ProviderID = @ProviderID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderID", PurchaseOrderM.ProviderID));
}
if (PurchaseOrderM.BillStatus != "0")
{
sql.AppendLine(" AND A.BillStatus = @BillStatus");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseOrderM.BillStatus));
}
if (PurchaseOrderM.FlowStatus != "0")
{
if (PurchaseOrderM.FlowStatus == "")
{
sql.AppendLine(" AND D.FlowStatus is null");
}
else
{
sql.AppendLine(" AND D.FlowStatus = @FlowStatus");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", PurchaseOrderM.FlowStatus));
}
}
string CanViewUser = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID.ToString();
sql.AppendLine(" and (a.CanViewUser like '%" + CanViewUser + "%' or '" + CanViewUser + "' = a.Creator or a.CanViewUser = '******' or a.CanViewUser is null )");
if (!string.IsNullOrEmpty(PurchaseOrderM.EFIndex) && !string.IsNullOrEmpty(PurchaseOrderM.EFDesc))
{
sql.AppendLine(" and a.ExtField" + PurchaseOrderM.EFIndex + " LIKE @EFDesc");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + PurchaseOrderM.EFDesc + "%"));
}
if (!string.IsNullOrEmpty(PurchaseOrderM.ProjectID))
{
sql.AppendLine(" AND a.ProjectID = @ProjectID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectID", PurchaseOrderM.ProjectID));
}
#endregion
comm.CommandText = sql.ToString();
//return SqlHelper.ExecuteSearch(comm);
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount);
}
public static DataTable GetPurchaseOrderAnaylise(PurchaseOrderModel PurchaseOrderM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
SqlCommand comm = new SqlCommand();
StringBuilder sql = new StringBuilder();
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
sql.AppendLine(" select isnull(d.ProductID,e.ProductID) as productID, Convert(char(20),Convert(numeric(22," + userInfo.SelPoint + "),isnull(d.OrderCount,0)))+' ' OrderCount, Convert(char(20),Convert(numeric(22," + userInfo.SelPoint + "),isnull(d.OrderPrice,0)))+' ' OrderPrice, Convert(char(20),Convert(numeric(22," + userInfo.SelPoint + "),isnull(e.RejectPrice,0)))+' ' RejectPrice, Convert(char(20),Convert(numeric(22," + userInfo.SelPoint + "),isnull(e.RejectCount,0)))+' ' RejectCount ");
sql.AppendLine(",isnull(d.ProviderID,e.ProviderID) as ProviderID,ISNULL(c.ProductName,'') AS ProductName,ISNULL(c.Specification,'')as Specification,isnull(f.CodeName,'') AS UnitName, ");
sql.AppendLine("isnull(g.CustName,'') as ProviderName,c.ProdNo as ProductNo ");
sql.AppendLine(" from ");
sql.AppendLine(" ( ");
sql.AppendLine(" select distinct B.ProductID,a.ProviderID,sum( isnull(b.ProductCount,0)) as OrderCount, sum(isnull(b.TotalPrice,0)) as OrderPrice ");
sql.AppendLine(" from Officedba.PurchaseOrder a ");
sql.AppendLine(" left outer join Officedba.PurchaseOrderDetail b on a.CompanyCD=b.CompanyCD and a.OrderNo=b.OrderNo ");
sql.AppendLine(" where a.BillStatus=2 and a.CompanyCD=@CompanyCD AND b.ProductNo is not null ");
if (PurchaseOrderM.Title != "")
{
sql.AppendLine(" AND A.ConfirmDate > @Title ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", PurchaseOrderM.Title + " 00:00:00"));
}
if (PurchaseOrderM.TypeID != "")
{
sql.AppendLine(" AND A.ConfirmDate < @TypeID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", PurchaseOrderM.TypeID + " 23:59:59"));
}
sql.AppendLine(" group by b.ProductID,a.ProviderID) as d ");
sql.AppendLine(" full join ");
sql.AppendLine(" ( select distinct b.ProductID,a.ProviderID,sum(isnull(b.ProductCount,0)) as RejectCount,sum(isnull(b.TotalPrice,0)) as RejectPrice ");
sql.AppendLine(" from Officedba.PurchaseReject a ");
sql.AppendLine(" left outer join Officedba.PurchaseRejectDetail b on a.CompanyCD=b.CompanyCD and a.RejectNo=b.RejectNo ");
sql.AppendLine(" where a.BillStatus=2 and a.CompanyCD=@CompanyCD AND b.ProductNo is not null ");
if (PurchaseOrderM.Title != "")
{
sql.AppendLine(" AND a.ConfirmDate > @Title1 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title1", PurchaseOrderM.Title + " 00:00:00"));
}
if (PurchaseOrderM.TypeID != "")
{
sql.AppendLine(" AND a.ConfirmDate < @TypeID1 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID1", PurchaseOrderM.TypeID + " 23:59:59"));
}
sql.AppendLine(" group by b.ProductID,a.ProviderID) as e on d.ProductID=e.ProductID and d.ProviderID=e.ProviderID ");
sql.AppendLine(" left outer join Officedba.ProductInfo c on isnull(d.ProductID,e.ProductID)=C.ID ");
sql.AppendLine(" LEFT outer JOIN officedba.CodeUnitType AS f ON c.UnitID=f.ID ");
sql.AppendLine(" LEFT outer JOIN officedba.ProviderInfo AS g ON isnull(d.ProviderID,e.ProviderID)=g.ID ");
sql.AppendLine(" where 1=1 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", PurchaseOrderM.CompanyCD));
if (PurchaseOrderM.OrderNo != "")
{
sql.AppendLine(" AND c.ProdNo like @ProductNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductNo", "%" + PurchaseOrderM.OrderNo + "%"));
}
if (PurchaseOrderM.ProviderID != "")
{
sql.AppendLine(" AND isnull(d.ProviderID,e.ProviderID)= @ProviderID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderID", PurchaseOrderM.ProviderID));
}
if (PurchaseOrderM.DeptID != "")
{
sql.AppendLine(" AND c.ProductName like @DeptID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", "%" + PurchaseOrderM.DeptID + "%"));
}
comm.CommandText = sql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount);
}
public static SqlCommand InsertPurchaseOrder(PurchaseOrderModel PurchaseOrderM)
{
SqlCommand comm = new SqlCommand();
#region SQL文
StringBuilder sqlPri = new StringBuilder();
sqlPri.AppendLine("INSERT INTO officedba.PurchaseOrder ");
sqlPri.AppendLine(" (CompanyCD ");
sqlPri.AppendLine(" ,OrderNo ");
sqlPri.AppendLine(" ,Title ");
sqlPri.AppendLine(" ,TypeID ");
sqlPri.AppendLine(" ,FromType ");
sqlPri.AppendLine(" ,CurrencyType ");
sqlPri.AppendLine(" ,Rate ");
sqlPri.AppendLine(" ,ProjectID ");
//sqlPri.AppendLine(" ,PurchaseDate ");
sqlPri.AppendLine(" ,OrderDate ");
sqlPri.AppendLine(" ,TheyDelegate ");
sqlPri.AppendLine(" ,OurDelegate ");
sqlPri.AppendLine(" ,ProviderID ");
sqlPri.AppendLine(" ,DeptID ");
sqlPri.AppendLine(" ,PayType ");
sqlPri.AppendLine(" ,MoneyType ");
sqlPri.AppendLine(" ,Purchaser ");
sqlPri.AppendLine(" ,TakeType ");
sqlPri.AppendLine(" ,CarryType ");
sqlPri.AppendLine(" ,ProviderBillID ");
sqlPri.AppendLine(" ,remark ");
sqlPri.AppendLine(" ,TotalPrice ");
sqlPri.AppendLine(" ,TotalTax ");
sqlPri.AppendLine(" ,TotalFee ");
sqlPri.AppendLine(" ,Discount ");
sqlPri.AppendLine(" ,DiscountTotal ");
sqlPri.AppendLine(" ,OtherTotal ");
sqlPri.AppendLine(" ,RealTotal ");
sqlPri.AppendLine(" ,isAddTax ");
sqlPri.AppendLine(" ,CountTotal ");
sqlPri.AppendLine(" ,isOpenbill ");
sqlPri.AppendLine(" ,Confirmor ");
sqlPri.AppendLine(" ,ConfirmDate ");
//sqlPri.AppendLine(" ,Closer ");
//sqlPri.AppendLine(" ,CloseDate ");
sqlPri.AppendLine(" ,BillStatus ");
sqlPri.AppendLine(" ,Creator ");
sqlPri.AppendLine(" ,CreateDate ");
sqlPri.AppendLine(" ,ModifiedDate ");
sqlPri.AppendLine(" ,ModifiedUserID ");
sqlPri.AppendLine(" ,CanViewUserName ");
sqlPri.AppendLine(" ,CanViewUser ");
sqlPri.AppendLine(" ,Attachment ");
sqlPri.Append(")");
sqlPri.AppendLine(" VALUES(@CompanyCD ");
sqlPri.AppendLine(" ,@OrderNo ");
sqlPri.AppendLine(" ,@Title ");
sqlPri.AppendLine(" ,@TypeID ");
sqlPri.AppendLine(" ,@FromType ");
sqlPri.AppendLine(" ,@CurrencyType ");
sqlPri.AppendLine(" ,@Rate ");
sqlPri.AppendLine(" ,@ProjectID ");
//sqlPri.AppendLine(" ,@PurchaseDate ");
sqlPri.AppendLine(" ,@OrderDate ");
sqlPri.AppendLine(" ,@TheyDelegate ");
sqlPri.AppendLine(" ,@OurDelegate ");
sqlPri.AppendLine(" ,@ProviderID ");
sqlPri.AppendLine(" ,@DeptID ");
sqlPri.AppendLine(" ,@PayType ");
sqlPri.AppendLine(" ,@MoneyType ");
sqlPri.AppendLine(" ,@Purchaser ");
sqlPri.AppendLine(" ,@TakeType ");
sqlPri.AppendLine(" ,@CarryType ");
sqlPri.AppendLine(" ,@ProviderBillID ");
sqlPri.AppendLine(" ,@remark ");
sqlPri.AppendLine(" ,@TotalPrice ");
sqlPri.AppendLine(" ,@TotalTax ");
sqlPri.AppendLine(" ,@TotalFee ");
sqlPri.AppendLine(" ,@Discount ");
sqlPri.AppendLine(" ,@DiscountTotal ");
sqlPri.AppendLine(" ,@OtherTotal ");
sqlPri.AppendLine(" ,@RealTotal ");
sqlPri.AppendLine(" ,@isAddTax ");
sqlPri.AppendLine(" ,@CountTotal ");
sqlPri.AppendLine(" ,@isOpenbill ");
sqlPri.AppendLine(" ,@Confirmor ");
sqlPri.AppendLine(" ,@ConfirmDate ");
//sqlPri.AppendLine(" ,@Closer ");
//sqlPri.AppendLine(" ,@CloseDate ");
sqlPri.AppendLine(" ,@BillStatus ");
sqlPri.AppendLine(" ,@Creator ");
sqlPri.AppendLine(" ,@CreateDate ");
sqlPri.AppendLine(" ,@ModifiedDate ");
sqlPri.AppendLine(" ,@ModifiedUserID ");
sqlPri.AppendLine(" ,@CanViewUserName ");
sqlPri.AppendLine(" ,@CanViewUser ");
sqlPri.AppendLine(" ,@Attachment");
sqlPri.Append(")");
sqlPri.AppendLine("set @IndexID = @@IDENTITY");
#endregion
#region 传参
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", PurchaseOrderM.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OrderNo", PurchaseOrderM.OrderNo));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", PurchaseOrderM.Title));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", PurchaseOrderM.TypeID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", PurchaseOrderM.FromType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectID", PurchaseOrderM.ProjectID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CurrencyType", PurchaseOrderM.CurrencyType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Rate", PurchaseOrderM.Rate));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@PurchaseDate", PurchaseOrderM.PurchaseDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OrderDate", PurchaseOrderM.OrderDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TheyDelegate", PurchaseOrderM.TheyDelegate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OurDelegate", PurchaseOrderM.OurDelegate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderID", PurchaseOrderM.ProviderID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", PurchaseOrderM.DeptID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PayType", PurchaseOrderM.PayType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@MoneyType", PurchaseOrderM.MoneyType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Purchaser", (PurchaseOrderM.Purchaser == null) ? DBNull.Value.ToString() : PurchaseOrderM.Purchaser.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeType", (PurchaseOrderM.TakeType == null) ? DBNull.Value.ToString() : PurchaseOrderM.TakeType.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CarryType", (PurchaseOrderM.CarryType == null) ? DBNull.Value.ToString() : PurchaseOrderM.CarryType.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderBillID", PurchaseOrderM.ProviderBillID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@remark", PurchaseOrderM.Remark));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalPrice", PurchaseOrderM.TotalPrice.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalTax", PurchaseOrderM.TotalTax.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalFee", PurchaseOrderM.TotalFee.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Discount", PurchaseOrderM.Discount.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DiscountTotal", PurchaseOrderM.DiscountTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OtherTotal", PurchaseOrderM.OtherTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@RealTotal", PurchaseOrderM.RealTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@isAddTax", PurchaseOrderM.isAddTax));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CountTotal", PurchaseOrderM.CountTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@isOpenbill", PurchaseOrderM.isOpenbill));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", PurchaseOrderM.Confirmor.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ConfirmDate", PurchaseOrderM.ConfirmDate.ToString()));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@Closer", PurchaseOrderM.Closer.ToString()));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@CloseDate", PurchaseOrderM.CloseDate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseOrderM.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", PurchaseOrderM.Creator.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", PurchaseOrderM.CreateDate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", PurchaseOrderM.ModifiedDate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", PurchaseOrderM.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CanViewUserName", PurchaseOrderM.CanUserName));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CanViewUser ", PurchaseOrderM.CanUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Attachment", PurchaseOrderM.Attachment));
SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int);
IndexID.Direction = ParameterDirection.Output;
comm.Parameters.Add(IndexID);
#endregion
comm.CommandText = sqlPri.ToString();
return comm;
}
public static SqlCommand UpdatePurchaseOrder(PurchaseOrderModel PurchaseOrderM)
{
SqlCommand comm = new SqlCommand();
#region SQL文
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE officedba.PurchaseOrder ");
sql.AppendLine(" SET CompanyCD = @CompanyCD ");
sql.AppendLine(" ,OrderNo = @OrderNo ");
sql.AppendLine(" ,Title = @Title ");
sql.AppendLine(" ,TypeID = @TypeID ");
sql.AppendLine(" ,FromType = @FromType ");
sql.AppendLine(" ,CurrencyType = @CurrencyType ");
sql.AppendLine(" ,Rate = @Rate ");
sql.AppendLine(" ,ProjectID = @ProjectID ");
//sql.AppendLine(" ,PurchaseDate = @PurchaseDate ");
sql.AppendLine(" ,OrderDate = @OrderDate ");
sql.AppendLine(" ,TheyDelegate = @TheyDelegate ");
sql.AppendLine(" ,OurDelegate = @OurDelegate ");
sql.AppendLine(" ,ProviderID = @ProviderID ");
sql.AppendLine(" ,DeptID = @DeptID ");
sql.AppendLine(" ,PayType = @PayType ");
sql.AppendLine(" ,MoneyType = @MoneyType ");
sql.AppendLine(" ,Purchaser = @Purchaser ");
sql.AppendLine(" ,TakeType = @TakeType ");
sql.AppendLine(" ,CarryType = @CarryType ");
sql.AppendLine(" ,ProviderBillID = @ProviderBillID ");
sql.AppendLine(" ,remark = @remark ");
sql.AppendLine(" ,TotalPrice = @TotalPrice ");
sql.AppendLine(" ,TotalTax = @TotalTax ");
sql.AppendLine(" ,TotalFee = @TotalFee ");
sql.AppendLine(" ,Discount = @Discount ");
sql.AppendLine(" ,DiscountTotal = @DiscountTotal ");
sql.AppendLine(" ,OtherTotal = @OtherTotal ");
sql.AppendLine(" ,RealTotal = @RealTotal ");
sql.AppendLine(" ,isAddTax = @isAddTax ");
sql.AppendLine(" ,CountTotal = @CountTotal ");
//sql.AppendLine(" ,isOpenbill = @isOpenbill ");
sql.AppendLine(" ,Confirmor = @Confirmor ");
sql.AppendLine(" ,ConfirmDate = @ConfirmDate ");
//sql.AppendLine(" ,Closer = @Closer ");
//sql.AppendLine(" ,CloseDate = @CloseDate ");
sql.AppendLine(" ,BillStatus = @BillStatus ");
//sql.AppendLine(" ,Creator = @Creator ");
//sql.AppendLine(" ,CreateDate = @CreateDate ");
sql.AppendLine(" ,ModifiedDate = getDate() ");
sql.AppendLine(" ,ModifiedUserID = @ModifiedUserID ");
sql.AppendLine(" ,Attachment = @Attachment ");
sql.AppendLine(" ,CanViewUserName=@UserName ");
sql.AppendLine(" ,CanViewUser=@CanViewUser ");
sql.AppendLine(" WHERE ID=@ID ");
#endregion
#region 传参
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", PurchaseOrderM.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OrderNo", PurchaseOrderM.OrderNo));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", PurchaseOrderM.Title));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", PurchaseOrderM.TypeID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", PurchaseOrderM.FromType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectID", PurchaseOrderM.ProjectID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CurrencyType", PurchaseOrderM.CurrencyType.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Rate", PurchaseOrderM.Rate.ToString()));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@PurchaseDate", PurchaseOrderM.PurchaseDate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OrderDate", PurchaseOrderM.OrderDate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TheyDelegate", PurchaseOrderM.TheyDelegate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OurDelegate", PurchaseOrderM.OurDelegate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderID", PurchaseOrderM.ProviderID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", PurchaseOrderM.DeptID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PayType", PurchaseOrderM.PayType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@MoneyType", PurchaseOrderM.MoneyType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Purchaser", PurchaseOrderM.Purchaser));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TakeType", PurchaseOrderM.TakeType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CarryType", PurchaseOrderM.CarryType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderBillID", PurchaseOrderM.ProviderBillID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@remark", PurchaseOrderM.Remark));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalPrice", PurchaseOrderM.TotalPrice.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalTax", PurchaseOrderM.TotalTax.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TotalFee", PurchaseOrderM.TotalFee.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Discount", PurchaseOrderM.Discount.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DiscountTotal", PurchaseOrderM.DiscountTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OtherTotal", PurchaseOrderM.OtherTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@RealTotal", PurchaseOrderM.RealTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@isAddTax", PurchaseOrderM.isAddTax));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CountTotal", PurchaseOrderM.CountTotal.ToString()));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@isOpenbill", PurchaseOrderM.isOpenbill));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", PurchaseOrderM.Confirmor.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ConfirmDate", PurchaseOrderM.ConfirmDate.ToString()));
//comm[index].Parameters.Add(SqlHelper.GetParameterFromString("@Closer", PurchaseOrderM.Closer.ToString()));
//comm[index].Parameters.Add(SqlHelper.GetParameterFromString("@CloseDate", PurchaseOrderM.CloseDate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseOrderM.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", PurchaseOrderM.Creator.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@UserName", PurchaseOrderM.CanUserName));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CanViewUser ", PurchaseOrderM.CanUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", PurchaseOrderM.CreateDate.ToString()));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", PurchaseOrderM.ModifiedDate.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", PurchaseOrderM.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Attachment", PurchaseOrderM.Attachment));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", PurchaseOrderM.ID.ToString()));
#endregion
comm.CommandText = sql.ToString();
return comm;
}
public static DataTable GetPurchaseOrder(PurchaseOrderModel PurchaseOrderM, string OrderBy)
{
try
{
DataTable dt = PurchaseOrderDBHelper.GetPurchaseOrder(PurchaseOrderM, OrderBy);
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable GetPurchaseOrder(PurchaseOrderModel PurchaseOrderM, string OrderBy)
{
SqlCommand comm = new SqlCommand();
#region SQL文
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT A.ID ");
sql.AppendLine(" , A.CompanyCD ");
sql.AppendLine(" , A.OrderNo ");
sql.AppendLine(" , A.Title AS OrderTitle ");
sql.AppendLine(" , A.TypeID ");
sql.AppendLine(" , E.TypeName ");
sql.AppendLine(" , A.Purchaser as PurchaserID ");
sql.AppendLine(" , B.EmployeeName AS PurchaserName ");
sql.AppendLine(" , A.ProviderID ");
sql.AppendLine(" , C.CustName AS ProviderName ");
sql.AppendLine(" , A.TotalPrice ");
sql.AppendLine(" , A.TotalTax ");
sql.AppendLine(" , A.TotalFee ");
sql.AppendLine(" , A.isOpenBill ");
sql.AppendLine(" , CASE A.isOpenBill WHEN '0' THEN '未建单' WHEN '1' THEN '已建单' END AS isOpenBillName");
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 '自动结单' end AS BillStatusName ");
sql.AppendLine(" , D.FlowStatus ");
sql.AppendLine(" ,case D.FlowStatus when '1' then '待审批' when '2' then '审批中' when '3' ");
sql.AppendLine(" then '审批通过' when '4' then '审批不通过' when '5' then '撤销审批' else '' end as FlowStatusName ");
sql.AppendLine(" FROM officedba.PurchaseOrder AS A ");
sql.AppendLine(" INNER JOIN officedba.EmployeeInfo AS B ON A.Purchaser = B.ID ");
sql.AppendLine(" INNER JOIN officedba.ProviderInfo AS C ON A.ProviderID = C.ID ");
sql.AppendLine(" LEFT JOIN officedba.FlowInstance AS D ON A.ID = D.BillID AND D.BillTypeFlag = 6 AND D.BillTypeCode = 4 ");
sql.AppendLine(" AND D.ID=(SELECT max(ID) FROM officedba.FlowInstance AS F WHERE A.ID = F.BillID AND F.BillTypeFlag = 6 AND F.BillTypeCode = 4 )");
sql.AppendLine(" LEFT JOIN officedba.CodePublicType AS E ON A.TypeID=E.ID ");
sql.AppendLine(" WHERE 1=1");
sql.AppendLine(" AND A.CompanyCD=@CompanyCD");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", PurchaseOrderM.CompanyCD));
if (PurchaseOrderM.OrderNo != "")
{
sql.AppendLine(" AND A.OrderNo like @OrderNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@OrderNo", "%" + PurchaseOrderM.OrderNo + "%"));
}
if (PurchaseOrderM.Title != "")
{
sql.AppendLine(" AND A.Title like @Title");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", "%" + PurchaseOrderM.Title + "%"));
}
if (PurchaseOrderM.TypeID != "")
{
sql.AppendLine(" AND A.TypeID = @TypeID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", PurchaseOrderM.TypeID));
}
if (PurchaseOrderM.DeptID != "")
{
sql.AppendLine(" AND A.DeptID = @DeptID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", PurchaseOrderM.DeptID));
}
if (PurchaseOrderM.Purchaser != "")
{
sql.AppendLine(" AND A.Purchaser = @Purchaser");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Purchaser", PurchaseOrderM.Purchaser));
}
if (PurchaseOrderM.FromType != "10")
{
sql.AppendLine(" AND A.FromType = @FromType");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", PurchaseOrderM.FromType));
}
if (PurchaseOrderM.ProviderID != "")
{
sql.AppendLine(" AND A.ProviderID = @ProviderID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProviderID", PurchaseOrderM.ProviderID));
}
if (!string.IsNullOrEmpty(PurchaseOrderM.ProjectID))
{
sql.AppendLine(" AND a.ProjectID = @ProjectID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectID", PurchaseOrderM.ProjectID));
}
if (PurchaseOrderM.BillStatus != "0")
{
sql.AppendLine(" AND A.BillStatus = @BillStatus");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseOrderM.BillStatus));
}
if (PurchaseOrderM.FlowStatus != "0")
{
if (PurchaseOrderM.FlowStatus == "")
{
sql.AppendLine(" AND D.FlowStatus is null");
}
else
{
sql.AppendLine(" AND D.FlowStatus = @FlowStatus");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", PurchaseOrderM.FlowStatus));
}
}
if (!string.IsNullOrEmpty(PurchaseOrderM.EFIndex) && !string.IsNullOrEmpty(PurchaseOrderM.EFDesc))
{
sql.AppendLine(" and a.ExtField" + PurchaseOrderM.EFIndex + " LIKE @EFDesc");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + PurchaseOrderM.EFDesc + "%"));
}
sql.AppendLine(" ORDER BY " + OrderBy + "");
#endregion
comm.CommandText = sql.ToString();
//return SqlHelper.ExecuteSearch(comm);
return SqlHelper.ExecuteSearch(comm);
}
public static DataTable GetPurchaseOrderAnaylise(PurchaseOrderModel PurchaseOrderM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
DataTable dt = PurchaseOrderDBHelper.GetPurchaseOrderAnaylise(PurchaseOrderM, pageIndex, pageCount, OrderBy, ref totalCount);
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 检索采购订单
/// </summary>
/// <param name="PurchaseOrderM">采购订单Model</param>
/// <returns>DataTable</returns>
///
public static DataTable GetPurchaseOrder(PurchaseOrderModel PurchaseOrderM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
DataTable dt = PurchaseOrderDBHelper.GetPurchaseOrder(PurchaseOrderM, pageIndex, pageCount, OrderBy, ref totalCount);
//DataColumn JoinName = new DataColumn();
//dt.Columns.Add("IsCite");
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// string ID = dt.Rows[i]["ID"].ToString();
// bool IsCite = PurchaseOrderDBHelper.IsCite(ID);
// dt.Rows[i]["IsCite"] = IsCite;
//}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新采购订单
/// </summary>
/// <param name="PurchaseOrderM">主表model</param>
/// <param name="PurchaseOrderDetailMList">明细表modellist</param>
/// <param name="ProductMStorList">用于回写库存的物品modellist</param>
/// <param name="ProductMList">用于回写计划或是合同的物品modellist</param>
/// <returns>DataTable</returns>
public static bool UpdatePurchaseOrder(PurchaseOrderModel PurchaseOrderM, List<PurchaseOrderDetailModel> PurchaseOrderDetailMList
, List<ProductModel> ProductMStorList, List<ProductModel> ProductMList, out string Reason, Hashtable htExtAttr)
{
try
{
//判断引用源单数量有没有超过，超过了就不让保存
if (!PurchaseOrderDBHelper.CanSave(PurchaseOrderDetailMList, out Reason))
return false;
//判断该单据有没有确认
//判断该单据是不是可以确认，包括引用的源单数量是不是合法，有没有到达库存报警上限
ArrayList lstUpdate = new ArrayList();
//更新主表
SqlCommand UpdatePri = PurchaseOrderDBHelper.UpdatePurchaseOrder(PurchaseOrderM);
lstUpdate.Add(UpdatePri);
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(PurchaseOrderM, htExtAttr, cmd);
if (htExtAttr.Count > 0)
lstUpdate.Add(cmd);
#endregion
string OrderNo = PurchaseOrderM.OrderNo;
//删除明细
SqlCommand DeleteDetail = PurchaseOrderDBHelper.DeletePurchaseOrderDetailSingle(OrderNo);
lstUpdate.Add(DeleteDetail);
//插入明细
foreach (PurchaseOrderDetailModel PurchaseOrderDetailM in PurchaseOrderDetailMList)
{
SqlCommand AddDetail = PurchaseOrderDBHelper.InsertPurchaseOrderDetail(PurchaseOrderDetailM, OrderNo);
lstUpdate.Add(AddDetail);
}
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//定义返回变量
bool isSucc = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//执行插入操作
try
{
isSucc = SqlHelper.ExecuteTransWithArrayList(lstUpdate);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (isSucc)
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
LogInfoModel logModel = InitLogInfo(OrderNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return isSucc;
}
catch (Exception ex)
{
throw ex;
}
}
public static bool InsertPurchaseOrder(PurchaseOrderModel PurchaseOrderM, List<PurchaseOrderDetailModel> PurchaseOrderDetailMList, out int IndexIDentity, out string Reason, Hashtable htExtAttr)
{
try
{
IndexIDentity = 0;
//判断引用源单数量有没有超过，超过了就不让保存
if (!PurchaseOrderDBHelper.CanSave(PurchaseOrderDetailMList, out Reason))
return false;
ArrayList lstAdd = new ArrayList();
//插入主表
SqlCommand AddPri = PurchaseOrderDBHelper.InsertPurchaseOrder(PurchaseOrderM);
lstAdd.Add(AddPri);
string OrderNo = PurchaseOrderM.OrderNo;
//插入明细
foreach (PurchaseOrderDetailModel PurchaseOrderDetailM in PurchaseOrderDetailMList)
{
SqlCommand AddDetail = PurchaseOrderDBHelper.InsertPurchaseOrderDetail(PurchaseOrderDetailM, OrderNo);
lstAdd.Add(AddDetail);
}
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(PurchaseOrderM, htExtAttr, cmd);
if (htExtAttr.Count > 0)
lstAdd.Add(cmd);
#endregion
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//定义返回变量
bool isSucc = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//执行插入操作
try
{
isSucc = SqlHelper.ExecuteTransWithArrayList(lstAdd);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (isSucc)
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
IndexIDentity = int.Parse(((SqlCommand)AddPri).Parameters["@IndexID"].Value.ToString());
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
IndexIDentity = 0;
}
LogInfoModel logModel = InitLogInfo(OrderNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return isSucc;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(PurchaseOrderModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.PurchaseOrder 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 OrderNo = @OrderNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@OrderNo", model.OrderNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}