public static bool CancelConfirmPurchaseArrive(PurchaseArriveModel Model, string DetailProductCount, string DetailFromBillNo, string DetailFromLineNo, string length, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
try
{
isSucc = PurchaseArriveDBHelper.CancelConfirmPurchaseArrive(Model, DetailProductCount, DetailFromBillNo, DetailFromLineNo, length, out strMsg);
//确认成功后调用“自动生成凭证”方法
if (isSucc == true)
{
string str = "";
XBase.Business.Office.FinanceManager.AutoVoucherBus.AntiConfirmVoucher("officedba.PurchaseArrive," + Model.ID, out str);
strMsg += str;
}
}
catch (Exception e)
{
strMsg += e.Message;
}
return isSucc;
}
public static bool CancelClosePurchaseArrive(PurchaseArriveModel Model)
{
return PurchaseArriveDBHelper.CancelClosePurchaseArrive(Model);
}
public static bool UpdatePurchaseArrive(PurchaseArriveModel model, string DetailProductID, string DetailProductNo, string DetailProductName, string DetailUnitID, string DetailProductCount, string DetailRequireDate, string DetailUnitPrice, string DetailTaxPrice, string DetailDiscount, string DetailTaxRate, string DetailTotalPrice, string DetailTotalFee, string DetailTotalTax, string DetailRemark, string DetailFromBillID, string DetailFromBillNo, string DetailFromLineNo, string DetailUsedUnitCount, string DetailUsedUnitID, string DetailUsedPrice, string length, string fflag2, string no, Hashtable ht)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
if (model.ID <= 0)
{
return false;
}
try
{
bool succ = false;
LogInfoModel logModel = InitLogInfo(no);
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//设置模块ID 模块ID请在ConstUtil中定义，以便维护
logModel.ModuleID = ConstUtil.MODULE_ID_PurchaseArrive_Add;
succ = PurchaseArriveDBHelper.UpdatePurchaseArrive(model, DetailProductID, DetailProductNo, DetailProductName, DetailUnitID, DetailProductCount, DetailRequireDate, DetailUnitPrice, DetailTaxPrice, DetailDiscount, DetailTaxRate, DetailTotalPrice, DetailTotalFee, DetailTotalTax, DetailRemark, DetailFromBillID, DetailFromBillNo, DetailFromLineNo, DetailUsedUnitCount, DetailUsedUnitID, DetailUsedPrice, length, fflag2, no, ht);
if (!succ)
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
else
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
LogDBHelper.InsertLog(logModel);
return succ;
}
catch (System.Exception ex)
{
throw ex;
}
}
public static bool ConfirmPurchaseArrive(PurchaseArriveModel Model, string DetailProductCount, string DetailFromBillNo, string DetailFromLineNo, string length, out string strMsg)
{
bool isSucc = false;//是否添加成功
strMsg = "";
try
{
isSucc = PurchaseArriveDBHelper.ConfirmPurchaseArrive(Model, DetailProductCount, DetailFromBillNo, DetailFromLineNo, length, out strMsg);
//确认成功后调用“自动生成凭证”方法
if (isSucc == true)
{
string str = "";
XBase.Business.Office.FinanceManager.AutoVoucherBus.AutoVoucherInsert(12, Model.TotalFee, "officedba.PurchaseArrive," + Model.ID, Model.CurrencyType + "," + Model.Rate, Model.ProviderID, out str);
//returnValue=0 业务单未设凭证模板,returnValue=1 企业不启用业务单自动生成凭证,returnValue = 2 企业不启用自动生成凭证自动登帐, returnValue = 3 自动生成凭证失败 ，returnValue = "4" 回写业务单登记凭证状态成功，returnValue = "5" 回写业务单登记凭证状态失败
strMsg += str;
}
}
catch (Exception e)
{
strMsg += e.Message;
}
return isSucc;
}
public static bool CancelClosePurchaseArrive(PurchaseArriveModel Model)
{
#region SQL文
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("update officedba.PurchaseArrive set ");
strSql.AppendLine(" Closer = @Closer");
strSql.AppendLine(" ,BillStatus=2");
strSql.AppendLine(" ,CloseDate=getdate()");
strSql.AppendLine(" where");
strSql.AppendLine(" CompanyCD= @CompanyCD");
strSql.AppendLine(" and ID= @ID");
#endregion
#region 参数
SqlParameter[] param = new SqlParameter[3];
param[0] = SqlHelper.GetParameter("@Closer", Model.Closer);
param[1] = SqlHelper.GetParameter("@CompanyCD", Model.CompanyCD);
param[2] = SqlHelper.GetParameter("@ID", Model.ID);
#endregion
SqlHelper.ExecuteTransSql(strSql.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
public static bool InsertPurchaseArrive(PurchaseArriveModel model, string DetailProductID, string DetailProductNo, string DetailProductName, string DetailUnitID, string DetailProductCount, string DetailRequireDate, string DetailUnitPrice, string DetailTaxPrice, string DetailDiscount, string DetailTaxRate, string DetailTotalPrice, string DetailTotalFee, string DetailTotalTax, string DetailRemark, string DetailFromBillID, string DetailFromLineNo, string DetailUsedUnitCount, string DetailUsedUnitID, string DetailUsedPrice, string length, out string ID, Hashtable ht)
{
try
{
bool succ = false;
LogInfoModel logModel = InitLogInfo(model.ArriveNo);
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//设置模块ID 模块ID请在ConstUtil中定义，以便维护
logModel.ModuleID = ConstUtil.MODULE_ID_PurchaseArrive_Add;
succ = PurchaseArriveDBHelper.InsertPurchaseArrive(model, DetailProductID, DetailProductNo, DetailProductName, DetailUnitID, DetailProductCount, DetailRequireDate, DetailUnitPrice, DetailTaxPrice, DetailDiscount, DetailTaxRate, DetailTotalPrice, DetailTotalFee, DetailTotalTax, DetailRemark, DetailFromBillID, DetailFromLineNo, DetailUsedUnitCount, DetailUsedUnitID, DetailUsedPrice, length, out ID, ht);
if (!succ)
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
else
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
LogDBHelper.InsertLog(logModel);
return succ;
}
catch (System.Exception ex)
{
throw ex;
}
}
public static bool ConfirmPurchaseArrive(PurchaseArriveModel Model, string DetailProductCount, string DetailFromBillNo, string DetailFromLineNo, string length, out string strMsg)
{
strMsg = "";
bool result = true;
if (isCanDo(Model.ID, "1"))
{
#region 确认
ArrayList listADD = new ArrayList();
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("update officedba.PurchaseArrive set ");
strSql.AppendLine(" Confirmor=@Confirmor");
strSql.AppendLine(" ,BillStatus=2");
strSql.AppendLine(" ,ConfirmDate=getdate()");
strSql.AppendLine(" where");
strSql.AppendLine(" CompanyCD=@CompanyCD");
strSql.AppendLine(" and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", Model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", Model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@ID", Model.ID));
comm.CommandText = strSql.ToString();
listADD.Add(comm);
#endregion
UserInfoUtil userInfo = SessionUtil.Session["UserInfo"] as UserInfoUtil;
#region 确认时回填订单中的到货数量
try
{
int lengs = Convert.ToInt32(length);
if (lengs > 0)
{
string[] ProductCount = DetailProductCount.Split(',');
string[] FromBillNo = DetailFromBillNo.Split(',');
string[] FromLineNo = DetailFromLineNo.Split(',');
string pp = "";
for (int i = 0; i < lengs; i++)
{
if (FromLineNo[i].ToString() != "")
{
if (userInfo.IsOverOrder || IsCanConfirm(FromBillNo[i].ToString(), FromLineNo[i].ToString(), Model.CompanyCD, ProductCount[i].ToString()))
{
System.Text.StringBuilder cmdsql = new System.Text.StringBuilder();
SqlCommand comms = new SqlCommand();
cmdsql.AppendLine("Update Officedba.PurchaseOrderDetail set ArrivedCount=isnull(ArrivedCount,0)+@ProductCount");
cmdsql.AppendLine(" where CompanyCD=@CompanyCD and OrderNo=@FromBillNo and SortNo=@FromLineNo");
comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", Model.CompanyCD));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductCount", ProductCount[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", FromBillNo[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", FromLineNo[i].ToString()));
comms.CommandText = cmdsql.ToString();
listADD.Add(comms);
}
else
{
if (pp != "")
{
pp += "," + (i + 1);
}
else
{
pp = "" + (i + 1);
}
strMsg += "第" + pp + "行的到货数量不能大于当前可用的到货数量,确认失败！";
result = false;
}
}
}
}
if (result)
{
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
strMsg = "确认成功！";
result = true;
}
else
{
strMsg = "确认失败！";
result = false;
}
}
return result;
}
catch (Exception ex)
{
strMsg = "确认失败！";
result = false;
return result;
throw ex;
}
#endregion
}
else
{//已经被其他人确认
strMsg = "已经确认的单据不可再次确认！";
result = false;
return result;
}
}
public static bool CancelConfirmPurchaseArrive(PurchaseArriveModel Model, string DetailProductCount, string DetailFromBillNo, string DetailFromLineNo, string length, out string strMsg)
{
bool isSuc = false;
strMsg = "";
//被财务模块调用不能取消确认
//判断单据是否为执行状态，非执行状态不能取消确认
if (isCanDo(Model.ID, "2"))
{
if (!IsCitePurchaseArrive(Model.ID))
{
string strSq = string.Empty;
//bool isSuc = false;
int iEmployeeID = 0;//员工id
string strUserID = string.Empty;//用户id
string strCompanyCD = string.Empty;//单位编码
SqlParameter[] paras = new SqlParameter[5];
iEmployeeID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
strUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
strCompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
strSq = "update officedba.PurchaseArrive set BillStatus='1' ";
strSq += " , Confirmor=@Confirmor, ConfirmDate=@ConfirmDate, ModifiedDate=getdate() ,ModifiedUserID=@UserID ";
strSq += " WHERE ID = @ID and CompanyCD=@CompanyCD";
paras[0] = new SqlParameter("@Confirmor", DBNull.Value);
paras[1] = new SqlParameter("@UserID", strUserID);
paras[2] = new SqlParameter("@CompanyCD", Model.CompanyCD);
paras[3] = new SqlParameter("@ID", Model.ID);
paras[4] = new SqlParameter("@ConfirmDate", DBNull.Value);
TransactionManager tran = new TransactionManager();
tran.BeginTransaction();
try
{
int lengs = Convert.ToInt32(length);
if (lengs > 0)
{
string[] ProductCount = DetailProductCount.Split(',');
string[] FromBillNo = DetailFromBillNo.Split(',');
string[] FromLineNo = DetailFromLineNo.Split(',');
for (int i = 0; i < lengs; i++)
{
StringBuilder cmdsql = new StringBuilder();
cmdsql.Append("Update Officedba.PurchaseOrderDetail set ArrivedCount=isnull(ArrivedCount,0)-@ProductCount");
cmdsql.Append(" where CompanyCD=@CompanyCD and OrderNo=@FromBillNo and SortNo=@FromLineNo");
SqlParameter[] param = {
new SqlParameter("@CompanyCD", Model.CompanyCD),
new SqlParameter("@ProductCount", ProductCount[i].ToString()),
new SqlParameter("@FromBillNo", FromBillNo[i].ToString()),
new SqlParameter("@FromLineNo", FromLineNo[i].ToString())
};
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, cmdsql.ToString(), param);
}
}
FlowDBHelper.OperateCancelConfirm(Model.CompanyCD, Convert.ToInt32(ConstUtil.CODING_RULE_PURCHASE), Convert.ToInt32(ConstUtil.BILL_TYPEFLAG_PURCHASE_ARRIVE), Model.ID, strUserID, tran);//撤销审批
SqlHelper.ExecuteNonQuery(tran.Trans, CommandType.Text, strSq, paras);
tran.Commit();
isSuc = true;
strMsg = "取消确认成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMsg = "取消确认失败！";
isSuc = false;
throw ex;
}
}
else
{//单据被调用了,不可以取消确认
isSuc = false;
strMsg = "该单据已被其它单据调用了，不允许取消确认！";
}
}
else
{//单据被别人抢先取消确认了
isSuc = false;
strMsg = "该单据已被其他用户取消确认，不可再次取消确认！";
}
return isSuc;
}
public static bool UpdatePurchaseArrive(PurchaseArriveModel model, string DetailProductID
, string DetailProductNo, string DetailProductName, string DetailUnitID
, string DetailProductCount, string DetailRequireDate, string DetailUnitPrice
, string DetailTaxPrice, string DetailDiscount, string DetailTaxRate, string DetailTotalPrice
, string DetailTotalFee, string DetailTotalTax, string DetailRemark, string DetailFromBillID
, string DetailFromBillNo, string DetailFromLineNo, string DetailUsedUnitCount
, string DetailUsedUnitID, string DetailUsedPrice, string length, string fflag2
, string no, Hashtable htExtAttr)
{
if (model.ID <= 0)
{
return false;
}
ArrayList listADD = new ArrayList();
bool result = false;
#region 修改采购合同到货通知单
StringBuilder sqlArrive = new StringBuilder();
sqlArrive.AppendLine("Update Officedba.PurchaseArrive set CompanyCD=@CompanyCD,");
sqlArrive.AppendLine("Title=@Title,FromType=@FromType,ProviderID=@ProviderID,Purchaser=@Purchaser,DeptID=@DeptID,");
sqlArrive.AppendLine("PayType=@PayType,TypeID=@TypeID,TakeType=@TakeType,CarryType=@CarryType,SendAddress=@SendAddress,");
sqlArrive.AppendLine("ReceiveOverAddress=@ReceiveOverAddress,CheckUserID=@CheckUserID,CheckDate=@CheckDate,CurrencyType=@CurrencyType,Rate=@Rate,");
sqlArrive.AppendLine("TotalMoney=@TotalMoney,TotalTax=@TotalTax,TotalFee=@TotalFee,Discount=@Discount,DiscountTotal=@DiscountTotal,");
sqlArrive.AppendLine("RealTotal=@RealTotal,isAddTax=@isAddTax,CountTotal=@CountTotal,remark=@remark,BillStatus=@BillStatus,");
sqlArrive.AppendLine("CreateDate=@CreateDate,ModifiedUserID=@ModifiedUserID,Confirmor=@Confirmor,");
sqlArrive.AppendLine("ConfirmDate=@ConfirmDate,Closer=@Closer,CloseDate=@CloseDate,OtherTotal=@OtherTotal,");
sqlArrive.AppendLine("Attachment=@Attachment,MoneyType=@MoneyType,ArriveDate=@ArriveDate,ProjectID=@ProjectID ,ModifiedDate=getDate(),CanViewUser =@CanUserID,CanViewUserName =@UserName where CompanyCD=@CompanyCD and ArriveNo='" + no + "'and ID=" + model.ID);
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameter("@UserName", model.CanUserName));
comm.Parameters.Add(SqlHelper.GetParameter("@CanUserID", model.CanUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title));
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
comm.Parameters.Add(SqlHelper.GetParameter("@ProviderID", model.ProviderID));
comm.Parameters.Add(SqlHelper.GetParameter("@Purchaser", model.Purchaser));
comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID));
comm.Parameters.Add(SqlHelper.GetParameter("@PayType", model.PayType));
comm.Parameters.Add(SqlHelper.GetParameter("@TypeID", model.TypeID));
comm.Parameters.Add(SqlHelper.GetParameter("@TakeType", model.TakeType));
comm.Parameters.Add(SqlHelper.GetParameter("@CarryType", model.CarryType));
comm.Parameters.Add(SqlHelper.GetParameter("@SendAddress", model.SendAddress));
comm.Parameters.Add(SqlHelper.GetParameter("@ReceiveOverAddress", model.ReceiveOverAddress));
comm.Parameters.Add(SqlHelper.GetParameter("@CheckUserID", model.CheckUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@CheckDate", model.CheckDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.CheckDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@CurrencyType", model.CurrencyType));
comm.Parameters.Add(SqlHelper.GetParameter("@Rate", model.Rate));
comm.Parameters.Add(SqlHelper.GetParameter("@TotalMoney", model.TotalMoney));
comm.Parameters.Add(SqlHelper.GetParameter("@TotalTax", model.TotalTax));
comm.Parameters.Add(SqlHelper.GetParameter("@TotalFee", model.TotalFee));
comm.Parameters.Add(SqlHelper.GetParameter("@Discount", model.Discount));
comm.Parameters.Add(SqlHelper.GetParameter("@DiscountTotal", model.DiscountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@RealTotal", model.RealTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@isAddTax", model.isAddTax));
comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@remark", model.remark));
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.CreateDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.ConfirmDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@Closer", model.Closer));
comm.Parameters.Add(SqlHelper.GetParameter("@CloseDate", model.CloseDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.CloseDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@OtherTotal", model.OtherTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@Attachment", model.Attachment));
comm.Parameters.Add(SqlHelper.GetParameter("@MoneyType", model.MoneyType));
comm.Parameters.Add(SqlHelper.GetParameter("@ArriveDate", model.ArriveDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.ArriveDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectID", model.ProjectID.HasValue
? SqlInt32.Parse(model.ProjectID.Value.ToString())
: SqlInt32.Null));
comm.CommandText = sqlArrive.ToString();
listADD.Add(comm);
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd, no);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
#region 删除到货通知单明细
System.Text.StringBuilder cmdddetail = new System.Text.StringBuilder();
cmdddetail.AppendLine("DELETE FROM officedba.PurchaseArriveDetail WHERE CompanyCD=@CompanyCD and ArriveNo=@ArriveNo");
SqlCommand comn = new SqlCommand();
comn.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comn.Parameters.Add(SqlHelper.GetParameter("@ArriveNo", no));
comn.CommandText = cmdddetail.ToString();
listADD.Add(comn);
#endregion
try
{
#region 重新插入到货通知单明细
int lengs = Convert.ToInt32(length);
if (lengs > 0)
{
string[] ProductID = DetailProductID.Split(',');
string[] ProductNo = DetailProductNo.Split(',');
string[] ProductName = DetailProductName.Split(',');
string[] UnitID = DetailUnitID.Split(',');
string[] ProductCount = DetailProductCount.Split(',');
string[] UnitPrice = DetailUnitPrice.Split(',');//单价
string[] TaxPrice = DetailTaxPrice.Split(',');//含税价
string[] TaxRate = DetailTaxRate.Split(',');//税率
string[] TotalPrice = DetailTotalPrice.Split(',');//金额
string[] TotalFee = DetailTotalFee.Split(',');//含税金额
string[] TotalTax = DetailTotalTax.Split(',');//税额
string[] Remark = DetailRemark.Split(',');
string[] FromBillID = DetailFromBillID.Split(',');
string[] FromLineNo = DetailFromLineNo.Split(',');
string[] UsedUnitCount = DetailUsedUnitCount.Split(',');
string[] UsedUnitID = DetailUsedUnitID.Split(',');
string[] UsedPrice = DetailUsedPrice.Split(',');
for (int i = 0; i < lengs; i++)
{
System.Text.StringBuilder cmdsql = new System.Text.StringBuilder();
SqlCommand comms = new SqlCommand();
cmdsql.AppendLine("INSERT INTO officedba.PurchaseArriveDetail");
cmdsql.AppendLine("(CompanyCD,ArriveNo,SortNo,FromType,FromBillID,FromLineNo,ProductID,ProductNo,ProductName,ProductCount,UnitID,UnitPrice,");
cmdsql.AppendLine("TaxPrice,TaxRate,TotalFee,TotalPrice,TotalTax,Remark,UsedUnitCount,UsedUnitID,ExRate,UsedPrice)");
cmdsql.AppendLine("VALUES (@CompanyCD,@ArriveNo,@SortNo,@FromType,@FromBillID,@FromLineNo,@ProductID,@ProductNo,@ProductName,@ProductCount,@UnitID,@UnitPrice,");
cmdsql.AppendLine("@TaxPrice,@TaxRate,@TotalFee,@TotalPrice,@TotalTax,@Remark2,@UsedUnitCount,@UsedUnitID,@ExRate,@UsedPrice)");
comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comms.Parameters.Add(SqlHelper.GetParameter("@ArriveNo", no));
comms.Parameters.Add(SqlHelper.GetParameter("@SortNo", i + 1));
comms.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
comms.Parameters.Add(SqlHelper.GetParameter("@FromBillID", FromBillID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", FromLineNo[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductNo", ProductNo[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductName", ProductName[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductCount", ProductCount[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@UnitID", UnitID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@UnitPrice", UnitPrice[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TaxPrice", TaxPrice[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TaxRate", TaxRate[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TotalFee", TotalFee[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TotalPrice", TotalPrice[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TotalTax", TotalTax[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@Remark2", Remark[i].ToString()));
if (UsedUnitCount.Length == lengs && !string.IsNullOrEmpty(UsedUnitCount[i]))
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", UsedUnitCount[i]));
}
else
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", DBNull.Value));
}
if (UsedUnitID.Length == lengs && !string.IsNullOrEmpty(UsedUnitID[i].Split('|')[0]))
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", UsedUnitID[i].Split('|')[0]));
}
else
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", DBNull.Value));
}
if (UsedUnitID.Length == lengs && UsedUnitID[i].Split('|').Length == 2 && !string.IsNullOrEmpty(UsedUnitID[i].Split('|')[1]))
{
comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", UsedUnitID[i].Split('|')[1]));
}
else
{
comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", DBNull.Value));
}
if (UsedPrice.Length == lengs && !string.IsNullOrEmpty(UsedPrice[i]))
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedPrice", UsedPrice[i]));
}
else
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedPrice", DBNull.Value));
}
comms.CommandText = cmdsql.ToString();
listADD.Add(comms);
}
}
#endregion
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
result = true;
}
return result;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(PurchaseArriveModel model, Hashtable htExtAttr, SqlCommand cmd, string no)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.PurchaseArrive 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 ArriveNo = @ArriveNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
if (!string.IsNullOrEmpty(no))
{
cmd.Parameters.AddWithValue("@ArriveNo", no);
}
else
{
cmd.Parameters.AddWithValue("@ArriveNo", model.ArriveNo);
}
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}
public static bool InsertPurchaseArrive(PurchaseArriveModel model, string DetailProductID, string DetailProductNo
, string DetailProductName, string DetailUnitID, string DetailProductCount
, string DetailRequireDate, string DetailUnitPrice, string DetailTaxPrice
, string DetailDiscount, string DetailTaxRate, string DetailTotalPrice
, string DetailTotalFee, string DetailTotalTax, string DetailRemark
, string DetailFromBillID, string DetailFromLineNo
, string DetailUsedUnitCount, string DetailUsedUnitID, string DetailUsedPrice
, string length, out string ID, Hashtable htExtAttr)
{
ArrayList listADD = new ArrayList();
bool result = false;
ID = "0";
#region 采购合同到货通知单添加SQL语句
StringBuilder sqlArrive = new StringBuilder();
sqlArrive.AppendLine("INSERT INTO officedba.PurchaseArrive");
sqlArrive.AppendLine("(CompanyCD,ArriveNo,Title,FromType,ProviderID,Purchaser,DeptID,PayType,TypeID,TakeType,");
sqlArrive.AppendLine("CarryType,SendAddress,ReceiveOverAddress,CheckUserID,CheckDate,CurrencyType,Rate,");
sqlArrive.AppendLine("TotalMoney,TotalTax,TotalFee,Discount,DiscountTotal,RealTotal,isAddTax,CountTotal,");
sqlArrive.AppendLine("remark,BillStatus,Creator,CreateDate,ModifiedDate,ModifiedUserID,Confirmor,");
sqlArrive.AppendLine("ConfirmDate,Closer,CloseDate,OtherTotal,Attachment,MoneyType,ArriveDate,ProjectID,CanViewUserName ,CanViewUser)");
sqlArrive.AppendLine("VALUES (@CompanyCD,@ArriveNo,@Title,@FromType,@ProviderID,@Purchaser,@DeptID,@PayType,@TypeID,@TakeType,");
sqlArrive.AppendLine("@CarryType,@SendAddress,@ReceiveOverAddress,@CheckUserID,@CheckDate,@CurrencyType,@Rate,");
sqlArrive.AppendLine("@TotalMoney,@TotalTax,@TotalFee,@Discount,@DiscountTotal,@RealTotal,@isAddTax,@CountTotal,");
sqlArrive.AppendLine("@remark,@BillStatus,@Creator,@CreateDate,getdate(),@ModifiedUserID,@Confirmor,");
sqlArrive.AppendLine("@ConfirmDate,@Closer,@CloseDate,@OtherTotal,@Attachment,@MoneyType,@ArriveDate,@ProjectID,@UserName,@CanUserID)");
sqlArrive.AppendLine("set @ID=@@IDENTITY");
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameter("@UserName", model.CanUserName));
comm.Parameters.Add(SqlHelper.GetParameter("@CanUserID", model.CanUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@ArriveNo", model.ArriveNo));
comm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title));
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
comm.Parameters.Add(SqlHelper.GetParameter("@ProviderID", model.ProviderID));
comm.Parameters.Add(SqlHelper.GetParameter("@Purchaser", model.Purchaser));
comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID));
comm.Parameters.Add(SqlHelper.GetParameter("@PayType", model.PayType));
comm.Parameters.Add(SqlHelper.GetParameter("@TypeID", model.TypeID));
comm.Parameters.Add(SqlHelper.GetParameter("@TakeType", model.TakeType));
comm.Parameters.Add(SqlHelper.GetParameter("@CarryType", model.CarryType));
comm.Parameters.Add(SqlHelper.GetParameter("@SendAddress", model.SendAddress));
comm.Parameters.Add(SqlHelper.GetParameter("@ReceiveOverAddress", model.ReceiveOverAddress));
comm.Parameters.Add(SqlHelper.GetParameter("@CheckUserID", model.CheckUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@CheckDate", model.CheckDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.CheckDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@CurrencyType", model.CurrencyType));
comm.Parameters.Add(SqlHelper.GetParameter("@Rate", model.Rate));
comm.Parameters.Add(SqlHelper.GetParameter("@TotalMoney", model.TotalMoney));
comm.Parameters.Add(SqlHelper.GetParameter("@TotalTax", model.TotalTax));
comm.Parameters.Add(SqlHelper.GetParameter("@TotalFee", model.TotalFee));
comm.Parameters.Add(SqlHelper.GetParameter("@Discount", model.Discount));
comm.Parameters.Add(SqlHelper.GetParameter("@DiscountTotal", model.DiscountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@RealTotal", model.RealTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@isAddTax", model.isAddTax));
comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@remark", model.remark));
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.CreateDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.ConfirmDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@Closer", model.Closer));
comm.Parameters.Add(SqlHelper.GetParameter("@CloseDate", model.CloseDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.CloseDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@OtherTotal", model.OtherTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@Attachment", model.Attachment));
comm.Parameters.Add(SqlHelper.GetParameter("@MoneyType", model.MoneyType));
comm.Parameters.Add(SqlHelper.GetParameter("@ArriveDate", model.ArriveDate == null
? SqlDateTime.Null
: SqlDateTime.Parse(model.ArriveDate.ToString())));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectID", model.ProjectID.HasValue
? SqlInt32.Parse(model.ProjectID.Value.ToString())
: SqlInt32.Null));
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int));
comm.CommandText = sqlArrive.ToString();
listADD.Add(comm);
#endregion
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd, string.Empty);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
try
{
#region 采购合同到货通知单明细
int lengs = Convert.ToInt32(length);
if (lengs > 0)
{
string[] ProductID = DetailProductID.Split(',');
string[] ProductNo = DetailProductNo.Split(',');
string[] ProductName = DetailProductName.Split(',');
string[] UnitID = DetailUnitID.Split(',');
string[] ProductCount = DetailProductCount.Split(',');
string[] UnitPrice = DetailUnitPrice.Split(',');//单价
string[] TaxPrice = DetailTaxPrice.Split(',');//含税价
string[] TaxRate = DetailTaxRate.Split(',');//税率
string[] TotalPrice = DetailTotalPrice.Split(',');//金额
string[] TotalFee = DetailTotalFee.Split(',');//含税金额
string[] TotalTax = DetailTotalTax.Split(',');//税额
string[] Remark = DetailRemark.Split(',');
string[] FromBillID = DetailFromBillID.Split(',');
string[] FromLineNo = DetailFromLineNo.Split(',');
string[] UsedUnitCount = DetailUsedUnitCount.Split(',');
string[] UsedUnitID = DetailUsedUnitID.Split(',');
string[] UsedPrice = DetailUsedPrice.Split(',');
for (int i = 0; i < lengs; i++)
{
System.Text.StringBuilder cmdsql = new System.Text.StringBuilder();
SqlCommand comms = new SqlCommand();
cmdsql.AppendLine("INSERT INTO officedba.PurchaseArriveDetail");
cmdsql.AppendLine("(CompanyCD,ArriveNo,SortNo,FromType,FromBillID,FromLineNo,ProductID,ProductNo,ProductName,ProductCount,UnitID,UnitPrice,");
cmdsql.AppendLine("TaxPrice,TaxRate,TotalFee,TotalPrice,TotalTax,Remark,UsedUnitCount,UsedUnitID,ExRate,UsedPrice)");
cmdsql.AppendLine("VALUES (@CompanyCD,@ArriveNo,@SortNo,@FromType,@FromBillID,@FromLineNo,@ProductID,@ProductNo,@ProductName,@ProductCount,@UnitID,@UnitPrice,");
cmdsql.AppendLine("@TaxPrice,@TaxRate,@TotalFee,@TotalPrice,@TotalTax,@Remark2,@UsedUnitCount,@UsedUnitID,@ExRate,@UsedPrice)");
comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comms.Parameters.Add(SqlHelper.GetParameter("@ArriveNo", model.ArriveNo));
comms.Parameters.Add(SqlHelper.GetParameter("@SortNo", i + 1));
comms.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
comms.Parameters.Add(SqlHelper.GetParameter("@FromBillID", FromBillID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", FromLineNo[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductNo", ProductNo[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductName", ProductName[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductCount", ProductCount[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@UnitID", UnitID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TaxPrice", TaxPrice[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TaxRate", TaxRate[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TotalFee", TotalFee[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TotalPrice", TotalPrice[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@TotalTax", TotalTax[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@Remark2", Remark[i].ToString()));
if (UsedUnitCount.Length == lengs && !string.IsNullOrEmpty(UsedUnitCount[i]))
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", UsedUnitCount[i]));
}
else
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", DBNull.Value));
}
if (UsedUnitID.Length == lengs && !string.IsNullOrEmpty(UsedUnitID[i].Split('|')[0]))
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", UsedUnitID[i].Split('|')[0]));
}
else
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", DBNull.Value));
}
if (UsedUnitID.Length == lengs && UsedUnitID[i].Split('|').Length == 2 && !string.IsNullOrEmpty(UsedUnitID[i].Split('|')[1]))
{
comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", UsedUnitID[i].Split('|')[1]));
// 多计量单位时重新计算单价
comms.Parameters.Add(SqlHelper.GetParameter("@UnitPrice", (decimal.Parse(UsedPrice[i])) / decimal.Parse(UsedUnitID[i].Split('|')[1])));
}
else
{
comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", DBNull.Value));
// 单价
comms.Parameters.Add(SqlHelper.GetParameter("@UnitPrice", UnitPrice[i].ToString()));
}
if (UsedPrice.Length == lengs && !string.IsNullOrEmpty(UsedPrice[i]))
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedPrice", UsedPrice[i]));
}
else
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedPrice", DBNull.Value));
}
comms.CommandText = cmdsql.ToString();
listADD.Add(comms);
}
}
#endregion
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
ID = comm.Parameters["@ID"].Value.ToString();
result = true;
}
return result;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取采购到货通知单
/// </summary>
/// <param name="companycd"></param>
/// <returns></returns>
public static DataTable GetPurchaseList(PurchaseArriveModel model)
{
try
{
return StorageInPurchaseDHHelper.GetPurchaseList(model);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取采购到货单
/// </summary>
/// <param name="companycd"></param>
/// <returns></returns>
public static DataTable GetPurchaseList(PurchaseArriveModel model)
{
string sql = "select ID,ArriveNo,Title,ISNULL(CONVERT(VARCHAR(10),CreateDate,21),'') AS CreateDate from officedba.PurchaseArrive where CompanyCD='" + model.CompanyCD + "' and BillStatus=2";
SqlCommand comm = new SqlCommand();
//添加公司代码参数
if (!string.IsNullOrEmpty(model.ArriveNo))
{
sql += " and ArriveNo like '%'+ @ArriveNo +'%'";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ArriveNo", model.ArriveNo));
}
if (!string.IsNullOrEmpty(model.Title))
{
sql += " and Title like '%'+ @Title +'%'";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));
}
comm.CommandText = sql;
return SqlHelper.ExecuteSearch(comm);
}