using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.Areas.TruckMng.Models; using DSWeb.Areas.TruckMng.Models.MsWl_HH_ZY; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.Areas.CommMng.Models; using System.Data.Common; using System.Web; using DSWeb.Areas.CommMng.DAL; using HcUtility.Core; using HcUtility.Logging; using DSWeb.TruckMng.Comm.Cookie; using WebSqlHelper; using System.Data.SqlClient; using DSWeb.Models; using HcUtility.Comm; namespace DSWeb.Areas.TruckMng.DAL.MsWl_HH_ZY { public partial class MsWl_HH_ZYDAL { #region Inquery DataList static public List GetDataList(string strCondition, string sort = null) { var strSql = new StringBuilder(); strSql.Append(" SELECT [GID],[OrgCode],[Destination],dbo.trimdate(ExpDate) ExpDate,[TruckNo],[ContractNo],[MBLNO],[CargoQty] "); strSql.Append(" ,[Tons],[Distance],[Pay_Distance],[Price_1],[Price_2],[Amount_1],[Amount_2],[Remark],[Paid_Amount_1], "); strSql.Append(" [Paid_Amount_2],[Amount_1]-[Paid_Amount_1] Remain_Amount_1,[Amount_2]-[Paid_Amount_2] Remain_Amount_2 "); strSql.Append(" ,case (select DISTINCT In_Province FROM City_Distance WHERE NAME LIKE '%'+hz.Destination+'%') when 0 then '省外' else '省内' end in_Province_ref ,HZSTATUS "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=HZSTATUS) as HZSTATUS_REF "); strSql.Append(" ,(select DISTINCT PROVINCENAME FROM City_Distance WHERE NAME LIKE '%'+hz.Destination+'%') PROVINCENAME "); strSql.Append(" ,(select DISTINCT AreaName FROM City_Distance WHERE NAME LIKE '%'+hz.Destination+'%') AreaName"); strSql.Append(" ,(select DISTINCT In_Province FROM City_Distance WHERE NAME LIKE '%'+hz.Destination+'%') In_Province"); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99033 and EnumValueID=( "); strSql.Append(" select BizProperty from tMsWlTruck where truckno=hz.truckno)) as BizPropertyREF "); strSql.Append(" ,(select shortname from info_client where gid=( "); strSql.Append(" select owner from tMsWlTruck where truckno=hz.truckno)) as BizPropertyREF "); strSql.Append(" ,BillingType,case BillingType when 0 then '吨.公里' when 1 then '车.公里' else '其他' end as BillingTypeREF"); strSql.Append(" ,Tax,Pay_Amount_2,(select Property from tmswlTruck where truckno=hz.truckno) Property"); strSql.Append(" ,TaxAmount_1,TaxAmount_2,agio_Amount_1,agio_Amount_2,AmountPaid_1,AmountPaid_2"); strSql.Append(" ,(case AmountPaid_1 when 1 then '是' else '否' end) AmountPaid_1_Ref,(case AmountPaid_2 when 1 then '是' else '否' end) AmountPaid_2_Ref"); strSql.Append(" , Price_3,Amount_3,agio_Amount_3"); strSql.Append(" FROM [tMsWl_HH_ZY] hz "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else strSql.Append(" order by expdate,truckno,contractno"); return SetData(strSql); } static public List GetDataList_2(string strCondition, string sort = null) { var strSql = new StringBuilder(); strSql.Append(" select Destination,orgcode,dbo.trimdate(expdate) expdate,TruckNo,dbo.[ZY_GetContractNo](TruckNo,expdate,Destination) contractno "); strSql.Append(" ,dbo.[ZY_GetMBLNO](TruckNo,expdate,Destination) MBLNO,Price_1,sum(Amount_1) Amount_1,Price_2,sum(Amount_2) Amount_2 "); strSql.Append(" ,case (select DISTINCT In_Province FROM City_Distance WHERE NAME LIKE '%'+hz.Destination+'%') when 0 then '省外' else '省内' end in_Province_ref "); strSql.Append(" ,sum(CargoQty) CargoQty,sum(tons) tons,Distance,Pay_Distance,sum(Paid_Amount_1) [Paid_Amount_1] "); strSql.Append(" ,sum(Paid_Amount_2) [Paid_Amount_2],sum([Amount_1]-[Paid_Amount_1]) Remain_Amount_1,sum([Amount_2]-[Paid_Amount_2]) Remain_Amount_2 ,HZSTATUS "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=HZSTATUS) as HZSTATUS_REF "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99033 and EnumValueID=( "); strSql.Append(" select BizProperty from tMsWlTruck where truckno=hz.truckno)) as BizProperty_REF "); strSql.Append(" ,(select DISTINCT PROVINCENAME FROM City_Distance WHERE NAME LIKE '%'+hz.Destination+'%') PROVINCENAME "); strSql.Append(" ,(select DISTINCT AreaName FROM City_Distance WHERE NAME LIKE '%'+hz.Destination+'%') AreaName"); strSql.Append(" ,(select DISTINCT In_Province FROM City_Distance WHERE NAME LIKE '%'+hz.Destination+'%') In_Province"); strSql.Append(" ,(select shortname from info_client where gid=( "); strSql.Append(" select owner from tMsWlTruck where truckno=hz.truckno)) as OWNER_REF "); strSql.Append(" ,sum(Tax) tax,sum(Pay_Amount_2) Pay_Amount_2,(select Property from tmswlTruck where truckno=hz.truckno) Property "); strSql.Append(" ,sum(TaxAmount_1) TaxAmount_1,sum(TaxAmount_2) TaxAmount_2,sum(agio_Amount_1) agio_Amount_1,sum(agio_Amount_2) agio_Amount_2 "); strSql.Append(" ,sum(AmountPaid_1) AmountPaid_1,sum(AmountPaid_2) AmountPaid_2"); strSql.Append(" ,(case sum(AmountPaid_1) when 0 then '否' else '是' end) AmountPaid_1_Ref "); strSql.Append(" ,(case sum(AmountPaid_2) when 0 then '否' else '是' end) AmountPaid_2_Ref "); strSql.Append(" FROM [tMsWl_HH_ZY] hz "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" group by Destination,expdate,TruckNo,Price_1,Price_2,orgcode,in_Province,Distance,pay_Distance,HZSTATUS "); var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else strSql.Append(" order by expdate,truckno,contractno"); return SetData_2(strSql); } static public MsWl_HH_ZYmb GetData(string condition) { var list = GetDataList(condition); if (list.Count > 0) return list[0]; return new MsWl_HH_ZYmb(); } private static List SetData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsWl_HH_ZYmb data = new MsWl_HH_ZYmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.OrgCode = Convert.ToString(reader["OrgCode"]); data.Destination = Convert.ToString(reader["Destination"]); data.ExpDate = Convert.ToString(reader["ExpDate"]); data.TruckNo = Convert.ToString(reader["TruckNo"]); data.ContractNo = Convert.ToString(reader["ContractNo"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.CargoQty = Convert.ToString(reader["CargoQty"]); data.Tons = Convert.ToString(reader["Tons"]); data.Distance = Convert.ToString(reader["Distance"]); data.Pay_Distance = Convert.ToString(reader["Pay_Distance"]); data.Price_1 = Convert.ToString(reader["Price_1"]); data.Price_2 = Convert.ToString(reader["Price_2"]); data.Amount_1 = Convert.ToString(reader["Amount_1"]); data.Amount_2 = Convert.ToString(reader["Amount_2"]); data.In_Province = Convert.ToString(reader["In_Province"]); data.In_Province_REF = Convert.ToString(reader["In_Province_REF"]); data.Remark = Convert.ToString(reader["Remark"]); data.Paid_Amount_1 = Convert.ToString(reader["Paid_Amount_1"]); data.Paid_Amount_2 = Convert.ToString(reader["Paid_Amount_2"]); data.Remain_Amount_1 = Convert.ToString(reader["Remain_Amount_1"]); data.Remain_Amount_2 = Convert.ToString(reader["Remain_Amount_2"]); data.HZSTATUS = Convert.ToString(reader["HZSTATUS"]); data.HZSTATUS_REF = Convert.ToString(reader["HZSTATUS_REF"]); data.BillingType = Convert.ToString(reader["BillingType"]); data.BillingTypeREF = Convert.ToString(reader["BillingTypeREF"]); data.Tax = Convert.ToString(reader["Tax"]); data.Pay_Amount_2 = Convert.ToString(reader["Pay_Amount_2"]); data.Property = Convert.ToString(reader["Property"]); data.TaxAmount_1 = Convert.ToString(reader["TaxAmount_1"]); data.TaxAmount_2 = Convert.ToString(reader["TaxAmount_2"]); data.agio_Amount_1 = Convert.ToString(reader["agio_Amount_1"]); data.agio_Amount_2 = Convert.ToString(reader["agio_Amount_2"]); data.AmountPaid_1 = Convert.ToString(reader["AmountPaid_1"]); data.AmountPaid_2 = Convert.ToString(reader["AmountPaid_2"]); data.AmountPaid_1_Ref = Convert.ToString(reader["AmountPaid_1_Ref"]); data.AmountPaid_2_Ref = Convert.ToString(reader["AmountPaid_2_Ref"]); data.Price_3 = Convert.ToString(reader["Price_3"]); data.Amount_3 = Convert.ToString(reader["Amount_3"]); data.agio_Amount_3 = Convert.ToString(reader["agio_Amount_3"]); data.AreaName = Convert.ToString(reader["AreaName"]); data.PROVINCENAME = Convert.ToString(reader["PROVINCENAME"]); #endregion headList.Add(data); } reader.Close(); } return headList; } private static List SetData_2(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsWl_HH_ZYmb data = new MsWl_HH_ZYmb(); #region Set DB data to Object //data.GID = Convert.ToString(reader["GID"]); data.OrgCode = Convert.ToString(reader["OrgCode"]); data.Destination = Convert.ToString(reader["Destination"]); data.ExpDate = Convert.ToString(reader["ExpDate"]); data.TruckNo = Convert.ToString(reader["TruckNo"]); data.ContractNo = Convert.ToString(reader["ContractNo"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.CargoQty = Convert.ToString(reader["CargoQty"]); data.Tons = Convert.ToString(reader["Tons"]); data.Distance = Convert.ToString(reader["Distance"]); data.Pay_Distance = Convert.ToString(reader["Pay_Distance"]); data.Price_1 = Convert.ToString(reader["Price_1"]); data.Price_2 = Convert.ToString(reader["Price_2"]); data.Amount_1 = Convert.ToString(reader["Amount_1"]); data.Amount_2 = Convert.ToString(reader["Amount_2"]); //data.In_Province = Convert.ToString(reader["In_Province"]); data.In_Province_REF = Convert.ToString(reader["In_Province_REF"]); //data.Remark = Convert.ToString(reader["Remark"]); data.BizProperty_REF = Convert.ToString(reader["BizProperty_REF"]); data.OWNER_REF = Convert.ToString(reader["OWNER_REF"]); data.Tax = Convert.ToString(reader["Tax"]); data.Pay_Amount_2 = Convert.ToString(reader["Pay_Amount_2"]); data.Property = Convert.ToString(reader["Property"]); data.TaxAmount_1 = Convert.ToString(reader["TaxAmount_1"]); data.TaxAmount_2 = Convert.ToString(reader["TaxAmount_2"]); data.agio_Amount_1 = Convert.ToString(reader["agio_Amount_1"]); data.agio_Amount_2 = Convert.ToString(reader["agio_Amount_2"]); data.AmountPaid_1 = Convert.ToString(reader["AmountPaid_1"]); data.AmountPaid_2 = Convert.ToString(reader["AmountPaid_2"]); data.AmountPaid_1_Ref = Convert.ToString(reader["AmountPaid_1_Ref"]); data.AmountPaid_2_Ref = Convert.ToString(reader["AmountPaid_2_Ref"]); data.AreaName = Convert.ToString(reader["AreaName"]); data.PROVINCENAME = Convert.ToString(reader["PROVINCENAME"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion public enum RowState : uint { Insert, Update, None } /* public static bool ImportExcelData(HttpRequestBase request, DataTable table, out string msg,string Price_1,String Price_2, out Int32 InsertCount, out Int32 UpdateCount, out string UnKnowenTruckNo, out List headList) { var isSucess = false; msg = ""; headList = new List(); if (table == null) throw new ArgumentNullException("table"); var billNoList = new List(); Database db = DatabaseFactory.CreateDatabase(); InsertCount = 0; UpdateCount = 0; UnKnowenTruckNo = ""; using (DbConnection connection = db.CreateConnection()) { connection.Open(); try { var orgCode = CookieConfig.GetCookie_OrgCode(request); var userCode = CookieConfig.GetCookie_UserCode(request); var userName = CookieConfig.GetCookie_UserName(request); var _i = -1; var ExpDate = ""; foreach (DataRow row in table.Rows) { _i++; if (_i < 1) continue; var _ExpDate = Convert.ToString(row["指令开票日期"]); _ExpDate = _ExpDate.Replace("/", "-"); _ExpDate = _ExpDate.Replace(".", "-"); if (_ExpDate == "") {} else { ExpDate = _ExpDate; } var TruckNo = Convert.ToString(row["承运车辆"]); TruckNo = TruckNo.Replace("-", ""); TruckNo = TruckNo.Trim(); if (TruckNo.Trim() == "") { continue; } var objTruckNo = db.ExecuteScalar(CommandType.Text, "select TruckNo from tMsWlTruck where TruckNo='" + TruckNo + "'"); var _TruckNo = Convert.ToString(objTruckNo); if (string.IsNullOrEmpty(_TruckNo)) { if (UnKnowenTruckNo == "") { UnKnowenTruckNo = "有如下车号在系统中不存在，请添加后重新导入：" + TruckNo; } else UnKnowenTruckNo = UnKnowenTruckNo + "/" + TruckNo; continue; } DbTransaction idbTran = connection.BeginTransaction(); var Destination = Convert.ToString(row["客户名称"]); var ContractNo = Convert.ToString(row["合同号"]); var CargoQty = Convert.ToString(row["合同数量(万支)"]); var _t = Convert.ToDouble(CargoQty) / 50; var Tons = Convert.ToString(_t); var _RowState = RowState.Insert; string strCheckRow = "select * from tMsWl_HH_ZY where ContractNo = '" + ContractNo + "'"; object statusObj = SqlHelper.ExecuteScalar(db.ConnectionString, CommandType.Text, strCheckRow, null); if (statusObj == null) { _RowState = RowState.Insert; } else { _RowState = RowState.Update; } if (_RowState == RowState.Insert) { #region 托单数据生成 //var billNo = PubSysDAL.GetBillNo("0119"); billNoList.Add(ContractNo); var gid = "JYHH_ZY" + Guid.NewGuid().ToString("N").ToUpper(); const string insertSql = @"insert into tMsWl_HH_ZY (GID,OrgCode,Destination,ExpDate,TruckNo,ContractNo,MBLNO,CargoQty ,Tons,Distance,Pay_Distance,Price_1,Price_2,Amount_1,Amount_2,In_Province,Remark,Tax,Pay_Amount_2) values(@GID,@OrgCode,@Destination,@ExpDate,@TruckNo,@ContractNo,@MBLNO,@CargoQty ,@Tons,(select isnull(Distance,-1) from City_Distance where name=@Destination), (select isnull(Pay_Distance,-1) from City_Distance where name=@Destination), @Price_1,@Price_2,@Amount_1,@Amount_2,@In_Province,@Remark,@Tax,@Pay_Amount_2)"; DbCommand cmd = db.GetSqlStringCommand(insertSql); db.AddInParameter(cmd, "GID", DbType.String, gid); db.AddInParameter(cmd, "OrgCode", DbType.String, orgCode); db.AddInParameter(cmd, "Destination", DbType.String, Destination); if (!string.IsNullOrEmpty(ExpDate)) { ExpDate = Convert.ToDateTime(ExpDate).ToString("yyyy-MM-dd").Trim(); } db.AddInParameter(cmd, "ExpDate", DbType.String, ExpDate); db.AddInParameter(cmd, "TruckNo", DbType.String, TruckNo); db.AddInParameter(cmd, "ContractNo", DbType.String, ContractNo); db.AddInParameter(cmd, "MBLNO", DbType.String, ""); db.AddInParameter(cmd, "CargoQty", DbType.String, CargoQty);//数量 //var Tons = Convert.ToInt32(row["合同数量(万支)"]); //Tons = Tons / 50;//50万支=1吨 db.AddInParameter(cmd, "Tons", DbType.String, Convert.ToString(Tons));//等价吨 db.AddInParameter(cmd, "Price_1", DbType.String, "0");//收费费率 db.AddInParameter(cmd, "Price_2", DbType.String, "0");//付费费率 db.AddInParameter(cmd, "Amount_1", DbType.String, "0");//应收 db.AddInParameter(cmd, "Amount_2", DbType.String, "0");//应付 db.AddInParameter(cmd, "In_Province", DbType.String, "0");//省内省外 db.AddInParameter(cmd, "Remark", DbType.String, "");//备注 db.AddInParameter(cmd, "Tax", DbType.String, "0");//税金 db.AddInParameter(cmd, "Pay_Amount_2", DbType.String, "0");//实际应付 db.ExecuteNonQuery(cmd, idbTran); idbTran.Commit(); InsertCount++; #endregion } else if (_RowState == RowState.Update) { #region 托单数据生成,插入 billNoList.Add(ContractNo); const string UpdSql = @" Update tMsWl_HH_ZY set Destination=@Destination,ExpDate=@ExpDate, CargoQty=@CargoQty,Tons=@Tons,Distance=(select isnull(Distance,0) from City_Distance where name=@Destination) ,Pay_Distance=(select isnull(Pay_Distance,0) from City_Distance where name=@Destination) ,Amount_1=@Tons*Price_1*(select isnull(Distance,0) from City_Distance where name=@Destination) ,Amount_2=@Tons*Price_2*(select isnull(Pay_Distance,0) from City_Distance where name=@Destination) where contractno=@ContractNo and HZSTATUS in (1,6)"; DbCommand cmd = db.GetSqlStringCommand(UpdSql); //var ExpDate = Convert.ToString(row["指令开票"]).Trim(); if (!string.IsNullOrEmpty(ExpDate)) { ExpDate = Convert.ToDateTime(ExpDate).ToString("yyyy-MM-dd").Trim(); } db.AddInParameter(cmd, "ExpDate", DbType.String, ExpDate); db.AddInParameter(cmd, "TruckNo", DbType.String, TruckNo); db.AddInParameter(cmd, "ContractNo", DbType.String, ContractNo); //db.AddInParameter(cmd, "MBLNO", DbType.String, Convert.ToString("")); db.AddInParameter(cmd, "Destination", DbType.String, Destination); db.AddInParameter(cmd, "CargoQty", DbType.String, CargoQty);//数量 //var Tons = Convert.ToInt32(row["合同数量(万支)"]); //Tons = Tons / 50;//50万支=1吨 db.AddInParameter(cmd, "Tons", DbType.String, Tons);//等价吨 db.ExecuteNonQuery(cmd, idbTran); idbTran.Commit(); UpdateCount++; //idbTran.Commit(); #endregion } } #region 获取新生成的托单费用 foreach (var billNo in billNoList) { var head = MsWl_HH_ZYDAL.GetData(" ContractNo='" + billNo + "'"); headList.Add(head); } #endregion isSucess = true; } catch (Exception exception) { //idbTran.Rollback(); isSucess = false; msg = exception.Message; } } return isSucess; } */ public static bool ImportExcelData ( HttpRequestBase request, DataTable table, string Price_1, string Price_2, string Price_3, out string msg, out Int32 InsertCount,out Int32 UpdateCount, out string UnKnowenTruckNo,out List headList) { var isSucess = false; msg = ""; headList = new List(); if (table == null) throw new ArgumentNullException("table"); var billNoList = new List(); Database db = DatabaseFactory.CreateDatabase(); InsertCount=0; UpdateCount=0; UnKnowenTruckNo = ""; var LastMBLNO = ""; for (int i = 0;i= 0 ) { sSql = "update tmswl_HH_ZY set Price_1=" + Price_1; sSql = sSql + ",Amount_1=(case BillingType when 0 then Tons*distance*" + Price_1 + " when 1 then distance*" + Price_1 + " end )"; } } if (Price_2 != "") { if (Convert.ToDouble(Price_2) >= 0) { if (sSql == "") { sSql = "update tmswl_HH_ZY set Price_2=" + Price_2 + ",Pay_Amount_2=(case BillingType when 0 then Tons*distance*" + Price_2 + " when 1 then distance*" + Price_2 + " end )"; } else if (Pay_Amount_2 == "") { sSql = sSql + ", Price_2=" + Price_2 + ",Pay_Amount_2=(case BillingType when 0 then Tons*distance*" + Price_2 + " when 1 then distance*" + Price_2 + " end )"; } else { sSql = sSql + ", Price_2=" + Price_2 ; } } } /* if (Price_3 != "") { if (Convert.ToDouble(Price_2) >= 0) { if (sSql == "") { sSql = "update tmswl_HH_ZY set Price_2=" + Price_2 + ",Pay_Amount_2=(case BillingType when 0 then Tons*distance*" + Price_2 + " when 1 then distance*" + Price_2 + " end )"; } else if (Pay_Amount_2 == "") { sSql = sSql + ", Price_2=" + Price_2 + ",Pay_Amount_2=(case BillingType when 0 then Tons*distance*" + Price_2 + " when 1 then distance*" + Price_2 + " end )"; } else { sSql = sSql + ", Price_2=" + Price_2; } } }*/ if (Pay_Amount_2 != "") { if (Convert.ToDouble(Pay_Amount_2) >= 0) { if (sSql == "") { sSql = "update tmswl_HH_ZY set Pay_Amount_2=" + Pay_Amount_2 ; } else { sSql = sSql + ", Pay_Amount_2=" + Pay_Amount_2; } } } sSql = sSql +" where "+ GIdSql; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSql, null); //事务提交 sqlTran.Commit(); result.Success = true; result.Message = "保存成功"; } catch (Exception execError) { result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; throw execError; } finally { SqlHelper.CloseConnection(); } } using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var sSql = ""; sSql = " update tmswl_HH_ZY set tax=Pay_Amount_2*0.045,Amount_2=Pay_Amount_2-Pay_Amount_2*0.045 "; sSql = sSql + " where TruckNo in(select truckno from tmswltruck where property=2) and " + GIdSql; sSql = sSql + " update tmswl_HH_ZY set tax=0,Amount_2=Pay_Amount_2 "; sSql = sSql + " where TruckNo in(select truckno from tmswltruck where property in(0,1)) and " + GIdSql; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSql, null); //事务提交 sqlTran.Commit(); result.Success = true; result.Message = "保存成功"; } catch (Exception execError) { result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; throw execError; } finally { SqlHelper.CloseConnection(); } } return result; } static public DBResult SetSettlement(string GIDList, string Amount_Settlement, string Type) { //var result = new DBResult(); var _TruckNoList = new List(); var strSql = new StringBuilder(); var S_Amount=Convert.ToDouble(Amount_Settlement); var _r = new DBResult(); if (Type == "1" || Type == "2") { strSql.Append(" select GID,isnull(Amount_" + Type + ",0),isnull(Paid_Amount_" + Type + ",0),isnull(Amount_" + Type + ",0)-isnull(Paid_Amount_" + Type + ",0) as needPay"); strSql.Append(" from tmswl_hh_zy where GID in(" + GIDList + ") order by expdate"); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var GID = Convert.ToString(reader["GID"]); var needPay = Convert.ToDouble(reader["needPay"]); if (needPay <= S_Amount)//剩余金额足以结算 { _r = DoSettlement(GID, needPay, Type); if (_r.Success == true) { S_Amount = S_Amount - needPay; continue; } } if (needPay > S_Amount)//剩余金额不够结算 { _r = DoSettlement(GID, S_Amount, Type); if (_r.Success == true) { break; } } } reader.Close(); } } if (Type == "3" || Type == "4") { var type = ""; if (Type == "3") { type = "1"; } if (Type == "4") { type = "2"; } strSql.Append(" select GID,isnull(Amount_" + type + ",0),isnull(Paid_Amount_" + type + ",0) Paid_Amount"); strSql.Append(" from tmswl_hh_zy where GID in(" + GIDList + ") order by expdate"); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var GID = Convert.ToString(reader["GID"]); var Paid_Amount = Convert.ToDouble(reader["Paid_Amount"]); if (Paid_Amount <= S_Amount)//剩余金额足以反结算 { _r = DoSettlement(GID, Paid_Amount, Type); if (_r.Success == true) { S_Amount = S_Amount - Paid_Amount; continue; } } if (Paid_Amount > S_Amount)//剩余金额不够结算 { _r = DoSettlement(GID, S_Amount, Type); if (_r.Success == true) { break; } } } reader.Close(); } } return _r; } static public DBResult DoSettlement(string GID, Double SettlementAmount, string type) { var result = new DBResult(); var _Mark = ""; if (type == "1" || type=="2") { _Mark = "+"; } if (type == "3") { type = "1"; _Mark = "-"; } if (type == "4") { type = "2"; _Mark = "-"; } using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append(" update tmswl_hh_ZY set Paid_Amount_" + type + "=Paid_Amount_" + type + _Mark + Convert.ToString(SettlementAmount) + " where GID='" + GID + "'"); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null); //事务提交 sqlTran.Commit(); result.Success = true; result.Message = "保存成功"; } catch (Exception execError) { result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; throw execError; } finally { SqlHelper.CloseConnection(); } } return result; } static public DBResult Submit(string GIDList, string OldStatus, string NewStatus) { var result = new DBResult(); using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var sSql = ""; sSql = "update tmswl_HH_ZY set HZSTATUS=" + NewStatus + " where GID in( " + GIDList + ") and HZSTATUS in(" + OldStatus + ")"; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSql, null); //事务提交 sqlTran.Commit(); result.Success = true; result.Message = "设置成功"; } catch (Exception execError) { result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; throw execError; } finally { SqlHelper.CloseConnection(); } } return result; } static public DBResult SetProvince(string ContractNo) { var result = new DBResult(); using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var sSql = ""; sSql = " update tmswl_HH_ZY set In_province=(select top 1 isnull(In_province,0) In_province from City_Distance where name=tmswl_HH_ZY.Destination) where ContractNo='" + ContractNo + "'"; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSql, null); //事务提交 sqlTran.Commit(); result.Success = true; result.Message = "保存成功"; } catch (Exception execError) { result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; throw execError; } finally { SqlHelper.CloseConnection(); } } return result; } #region 明细表 #endregion #region 参照部分 /* internal static List GetDrvCodeList(string userid, string usercode, string orgcode) { var rangstr = GetRangDAStr("", userid, usercode, orgcode); var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" DrvCode,DrvName,DrvCode + '-' + DrvName as CodeAndName"); strSql.Append(" from tMsWlDriver"); if (!string.IsNullOrEmpty(rangstr)) { strSql.Append(" where " + rangstr); } var dataList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var data = new MsWlTruckRefMsWlDriver(); #region Set DB data to Object data.DrvCode = Convert.ToString(reader["DrvCode"]); data.DrvName = Convert.ToString(reader["DrvName"]); data.CodeAndName = Convert.ToString(reader["CodeAndName"]); #endregion dataList.Add(data); } reader.Close(); } return dataList; } public static string GetRangDAStr(string tb, string userid, string usercode, string orgcode) { string str = ""; var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" VISIBLERANGE,OPERATERANGE "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='modTruckCar' and USERID='" + userid + "' and ISDELETE=0"); string visiblerange = "4"; string operaterange = "4"; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { visiblerange = Convert.ToString(reader["VISIBLERANGE"]); operaterange = Convert.ToString(reader["OPERATERANGE"]); break; } reader.Close(); } if (visiblerange == "4") { str = "1=2"; } else if (visiblerange == "3") { if (tb == "index") { str = " UserCode='" + usercode + "'"; } else { str = " UPPER(OrgCode)='" + orgcode + "'"; } } else if (visiblerange == "2") { str = " UPPER(OrgCode)='" + orgcode + "'"; } else if (visiblerange == "1") { str = " UPPER(OrgCode)='" + orgcode + "'"; } return str; } */ #endregion } }