using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.Areas.TruckMng.Models; using DSWeb.Areas.TruckMng.Models.TMSRPT; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.Areas.CommMng.Models; using System.Web; using System.Data.Common; using DSWeb.TruckMng.Comm.Cookie; using DSWeb.TruckMng.Helper.Repository; using HcUtility.Comm; using DSWeb.MvcShipping.Helper; namespace DSWeb.Areas.TruckMng.DAL.TMSRPT { public partial class TMSRPTDAL { #region 主表 static public List GetDataList(string strCondition, string userid, string usercode, string orgcode, string sort = null) { /* var rangstr = GetRangDAStr("", userid, usercode, orgcode); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } }*/ var strSql = new StringBuilder(); strSql.Append("SELECT [GID],[COMPANYID],[ACCDATE],dbo.trimdate(EXPDATEBGN) EXPDATEBGN,dbo.trimdate(EXPDATEEND) EXPDATEEND,[FUELPRICE],[REMARK],[OP],dbo.trimdate(OPDATE) OPDATE"); strSql.Append(",(select name from company where GID=COMPANYID) COMPANYNAME"); strSql.Append(",[AUDITOR],dbo.trimtime(AUDITTIME) AUDITTIME,[BLSTATUS],[ISDELETE],[DELETEUSER],dbo.trimtime(DELETETIME) DELETETIME"); strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99035 and EnumValueID=BLSTATUS) as BLSTATUS_REF"); strSql.Append(",convert(bigint ,TimeMark) as TimeMark"); strSql.Append(" FROM TMSRPT_MONTH where ISDELETE=0 "); if (strCondition.Trim() != String.Empty) { strSql.Append(" and " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } return SetData(strSql); } static public TMSRPT_MONTH GetData ( string condition, string userid, string usercode, string orgcode ) { var list = GetDataList(condition, userid, usercode,orgcode); if (list.Count > 0) return list[0]; return new TMSRPT_MONTH(); } 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()) { TMSRPT_MONTH data = new TMSRPT_MONTH(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.COMPANYID = Convert.ToString(reader["COMPANYID"]); data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.EXPDATEBGN = Convert.ToString(reader["EXPDATEBGN"]); data.EXPDATEEND = Convert.ToString(reader["EXPDATEEND"]); data.FUELPRICE = Convert.ToString(reader["FUELPRICE"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.OP = Convert.ToString(reader["OP"]); data.OPDATE = Convert.ToString(reader["OPDATE"]); data.AUDITOR = Convert.ToString(reader["AUDITOR"]); data.AUDITTIME = Convert.ToString(reader["AUDITTIME"]); data.BLSTATUS = Convert.ToString(reader["BLSTATUS"]); data.BLSTATUS_REF = Convert.ToString(reader["BLSTATUS_REF"]); data.ISDELETE = Convert.ToString(reader["ISDELETE"]); data.DELETEUSER = Convert.ToString(reader["DELETEUSER"]); data.DELETETIME = Convert.ToString(reader["DELETETIME"]); data.TimeMark = Convert.ToDecimal(reader["TimeMark"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 明细表 static public List GetBodyList(string strCondition) { var strSql = new StringBuilder(); strSql.Append(" select GID,LINKGID,COMPANYID,(select name from company where GID=COMPANYID) COMPANYNAME "); strSql.Append(" ,(select ACCDATE from TMSRPT_MONTH where GID=LINKGID) ACCDATE "); strSql.Append(" ,TRUCKNO,(select LoadCount from tmswltruck where truckno=TMSRPT_MONTH_TRUCKDETAIL.TRUCKNO) LoadCount "); strSql.Append(" ,(select trunkbrand from tmswltruck where truckno=TMSRPT_MONTH_TRUCKDETAIL.TRUCKNO) TruckSpec "); strSql.Append(" ,AMOUNT,ZJF,BXF,GZJZ,SBF,GJJ,FUELPRICE,FUELAMOUNT,LQF,REPAIRAMOUNT,TYREAMOUNT,OTHERAMOUNT,TAX,TRUCKCOST,TRUCKPROFIT_M "); strSql.Append(" ,CARDAYS,WORKDAYS,USABLEDAYS,USABLERATE,WORKRATE,REPAIRDAYS,NOGOODDAYS,NODRVDAYS,RATEDMIL,OVERLOADMIL,TEU,TONS "); strSql.Append(" ,MILPERDAY,REALFUEL,RATEDFUEL,REMAINFUEL,FUELAMOUNTRATE,OTHERPAY,OTHERPAYREMARK,REMARK "); strSql.Append(" from TMSRPT_MONTH_TRUCKDETAIL "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" order by TRUCKNO "); return SetBodyData(strSql); } private static List SetBodyData ( StringBuilder strSql ) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { TMSRPT_MONTH_TRUCKDETAIL data = new TMSRPT_MONTH_TRUCKDETAIL(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.LINKGID = Convert.ToString(reader["LINKGID"]); data.COMPANYID = Convert.ToString(reader["COMPANYID"]); data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]); data.LoadCount = Convert.ToString(reader["LoadCount"]); data.TruckSpec = Convert.ToString(reader["TruckSpec"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.ZJF = Convert.ToString(reader["ZJF"]); data.BXF = Convert.ToString(reader["BXF"]); data.GZJZ = Convert.ToString(reader["GZJZ"]); data.SBF = Convert.ToString(reader["SBF"]); data.GJJ = Convert.ToString(reader["GJJ"]); data.FUELPRICE = Convert.ToString(reader["FUELPRICE"]); data.FUELAMOUNT = Convert.ToString(reader["FUELAMOUNT"]); data.LQF = Convert.ToString(reader["LQF"]); data.REPAIRAMOUNT = Convert.ToString(reader["REPAIRAMOUNT"]); data.TYREAMOUNT = Convert.ToString(reader["TYREAMOUNT"]); data.OTHERAMOUNT = Convert.ToString(reader["OTHERAMOUNT"]); data.TAX = Convert.ToString(reader["TAX"]); data.TRUCKCOST = Convert.ToString(reader["TRUCKCOST"]); data.TRUCKPROFIT_M = Convert.ToString(reader["TRUCKPROFIT_M"]); data.CARDAYS = Convert.ToString(reader["CARDAYS"]); data.WORKDAYS = Convert.ToString(reader["WORKDAYS"]); data.USABLEDAYS = Convert.ToString(reader["USABLEDAYS"]); data.USABLERATE = Convert.ToString(reader["USABLERATE"]); data.WORKRATE = Convert.ToString(reader["WORKRATE"]); data.REPAIRDAYS = Convert.ToString(reader["REPAIRDAYS"]); data.NOGOODDAYS = Convert.ToString(reader["NOGOODDAYS"]); data.NODRVDAYS = Convert.ToString(reader["NODRVDAYS"]); data.RATEDMIL = Convert.ToString(reader["RATEDMIL"]); data.OVERLOADMIL = Convert.ToString(reader["OVERLOADMIL"]); data.TEU = Convert.ToString(reader["TEU"]); data.TONS = Convert.ToString(reader["TONS"]); data.MILPERDAY = Convert.ToString(reader["MILPERDAY"]); data.REALFUEL = Convert.ToString(reader["REALFUEL"]); data.RATEDFUEL = Convert.ToString(reader["RATEDFUEL"]); data.REMAINFUEL = Convert.ToString(reader["REMAINFUEL"]); data.FUELAMOUNTRATE = Convert.ToString(reader["FUELAMOUNTRATE"]); data.OTHERPAY = Convert.ToString(reader["OTHERPAY"]); data.OTHERPAYREMARK = Convert.ToString(reader["OTHERPAYREMARK"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } #endregion #region 明细表 static public List GetCompanyDetailList ( string strCondition ) { var strSql = new StringBuilder(); strSql.Append(" select (select name from company c where c.gid=md.COMPANYID) companyname "); strSql.Append(" ,(select accdate from TMSRPT_MONTH where gid=md.LINKGID) accdate "); strSql.Append(" ,(select count(*) from (select distinct truckno from TMSRPT_MONTH_TRUCKDETAIL where linkgid=md.LINKGID)t) TRUCKCOUNT "); strSql.Append(" ,sum(amount) AMOUNT,sum(TRUCKCOST) TRUCKCOST,sum(md.TRUCKPROFIT_M) TRUCKPROFIT_M "); strSql.Append(" ,sum(md.CARDAYS) CARDAYS,sum(md.RATEDMIL) RATEDMIL,sum(md.OVERLOADMIL) OVERLOADMIL "); strSql.Append(" ,sum(md.TEU) TEU,sum(md.TONS) TONS "); strSql.Append(" ,isnull((select sum(TEU) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='集运' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) CTN_TEU "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='集运' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) CTN_AMOUNT "); strSql.Append(" ,isnull((select sum(TONS) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='散货' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) SH_TONS "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='散货' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) SH_AMOUNT "); strSql.Append(" ,isnull((select sum(TEU) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='码头倒箱' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) PORTCTN_TEU "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='码头倒箱' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) PORTCTN_AMOUNT "); strSql.Append(" ,isnull((select sum(TONS) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='大件' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) DJ_TONS "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='大件' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) DJ_AMOUNT "); strSql.Append(" ,isnull((select sum(TONS) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='危险品' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) WXP_TEU "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='危险品' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) WXP_AMOUNT "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='危险品' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) WXP_AMOUNT "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='物流托管' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) WLTG_AMOUNT "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='第三方' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) DSF_AMOUNT "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='第三方代理' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) DSFDL_AMOUNT "); strSql.Append(" ,isnull((select sum(AMOUNT) from TMSRPT_MONTH_TRUCKDETAIL t1 where t1.PcBillType='其他' and t1.COMPANYID=md.COMPANYID and t1.LINKGID=md.LINKGID ),0) OTHER_AMOUNT "); strSql.Append(" from TMSRPT_MONTH_TRUCKDETAIL md "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" group by COMPANYID,LINKGID "); return SetCompanyDetailData(strSql); } private static List SetCompanyDetailData ( StringBuilder strSql ) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { TMSRPT_MONTH_COMPANYDETAIL data = new TMSRPT_MONTH_COMPANYDETAIL(); #region Set DB data to Object data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.TRUCKCOUNT = Convert.ToString(reader["TRUCKCOUNT"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.TRUCKCOST = Convert.ToString(reader["TRUCKCOST"]); data.TRUCKPROFIT_M = Convert.ToString(reader["TRUCKPROFIT_M"]); data.CARDAYS = Convert.ToString(reader["CARDAYS"]); data.RATEDMIL = Convert.ToString(reader["RATEDMIL"]); data.OVERLOADMIL = Convert.ToString(reader["OVERLOADMIL"]); data.TEU = Convert.ToString(reader["TEU"]); data.TONS = Convert.ToString(reader["TONS"]); data.CTN_TEU = Convert.ToString(reader["CTN_TEU"]); data.CTN_AMOUNT = Convert.ToString(reader["CTN_AMOUNT"]); data.WXP_TEU = Convert.ToString(reader["WXP_TEU"]); data.WXP_AMOUNT = Convert.ToString(reader["WXP_AMOUNT"]); data.PORTCTN_TEU = Convert.ToString(reader["PORTCTN_TEU"]); data.PORTCTN_AMOUNT = Convert.ToString(reader["PORTCTN_AMOUNT"]); data.DJ_TONS = Convert.ToString(reader["DJ_TONS"]); data.DJ_AMOUNT = Convert.ToString(reader["DJ_AMOUNT"]); data.SH_TONS = Convert.ToString(reader["SH_TONS"]); data.SH_AMOUNT = Convert.ToString(reader["SH_AMOUNT"]); data.WLTG_AMOUNT = Convert.ToString(reader["WLTG_AMOUNT"]); data.DSF_AMOUNT = Convert.ToString(reader["DSF_AMOUNT"]); data.DSFDL_AMOUNT = Convert.ToString(reader["DSFDL_AMOUNT"]); data.OTHER_AMOUNT = Convert.ToString(reader["OTHER_AMOUNT"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } #endregion #region 通过分公司获取此次应当使用的会计期间。 static public string GetACCDATE(string COMPANYID) { var strSql = new StringBuilder(); strSql.Append(" select max(accdate) ACCDATE from TMSRPT_MONTH where COMPANYID='" + COMPANYID + "' and isnull(ISDELETE,0)=0 "); return SetACCDATE(strSql); } private static string SetACCDATE(StringBuilder strSql) { //var headList = new List(); var ACCDATE = ""; var ACCDATE2 = ""; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ACCDATE = Convert.ToString(reader["ACCDATE"]); } reader.Close(); } if (ACCDATE != "") { var date1 = Convert.ToDateTime(ACCDATE); var date2 = date1.AddMonths(1); ACCDATE2 = date2.ToString("yyyy-MM"); return ACCDATE2; } else { return ""; } } static public string GetBeforeACCDATE(TMSRPT_MONTH head) { var ACCDATE2 = ""; if (head.ACCDATE != "") { var date1 = Convert.ToDateTime(head.ACCDATE); var date2 = date1.AddMonths(-1); ACCDATE2 = date2.ToString("yyyy-MM"); return ACCDATE2; } else { return ""; } } #endregion #region 导入excel public enum RowState : uint { Insert, Update, None } public static bool ImportExcelData ( HttpRequestBase request, DataTable table, out string msg, out Int32 InsertCount, out Int32 UpdateCount, string TMSRPT_GID, 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 UnKnowenAirPort = ""; using (DbConnection connection = db.CreateConnection()) //using (SqlTransaction idbTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { 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 = ""; var TruckNoList = ""; foreach (DataRow row in table.Rows) { var TRUCKNO = Convert.ToString(row["车号"]); if (TruckNoList == "") { TruckNoList = "'" + TRUCKNO + "'"; } else { TruckNoList += ",'" + TRUCKNO + "'"; } } var TruckList = GetBodyList(" LINKGID='" + TMSRPT_GID + "' and TRUCKNO in(" + TruckNoList + ") "); //var insertList = new List(); var DelList = new List(); foreach (DataRow row in table.Rows) { _i++; //if (_i < 1) continue; //var _ACCDATE = ACCDATE; //var _p = 0; var TRUCKNO = Convert.ToString(row["车号"]); var _success = false; foreach(var _TD in TruckList){ _success = false; if (_TD.TRUCKNO == TRUCKNO) { if (row["工资奖金"] != DBNull.Value) _TD.GZJZ = Convert.ToString(row["工资奖金"]); if (row["社保费"] != DBNull.Value) _TD.SBF = Convert.ToString(row["社保费"]); if (row["公积金"] != DBNull.Value) _TD.GJJ = Convert.ToString(row["公积金"]); if (row["税金"] != DBNull.Value) _TD.TAX = Convert.ToString(row["税金"]); if (row["工作车日"] != DBNull.Value) _TD.WORKDAYS = Convert.ToString(row["工作车日"]); if (row["无货日"] != DBNull.Value) _TD.NOGOODDAYS = Convert.ToString(row["无货日"]); if (row["无人日"] != DBNull.Value) _TD.NODRVDAYS = Convert.ToString(row["无人日"]); if (row["其他费用"] != DBNull.Value) _TD.OTHERPAY = Convert.ToString(row["其他费用"]); //需求编号SR2017042600006 //start if (row["修理车日"] != DBNull.Value) _TD.REPAIRDAYS = Convert.ToString(row["修理车日"]); //end _TD.TRUCKCOST =Convert.ToString(float.Parse(_TD.TRUCKCOST) - (float.Parse(_TD.OTHERPAY) + float.Parse(_TD.TAX) + float.Parse(_TD.GJJ) + float.Parse(_TD.SBF) + float.Parse(_TD.GZJZ))); UpdateCount += 1; _success = true; if (_TD.TRUCKPROFIT_M=="") { _TD.TRUCKPROFIT_M = "0"; } if (_TD.ZJF=="") { _TD.ZJF = "0"; } /* if (_TD.GZJZ == "") _TD.GZJZ = "0"; if (_TD.SBF == "") _TD.SBF = "0"; if (_TD.GJJ == "") _TD.GJJ = "0"; if (_TD.TAX == "") _TD.TAX = "0"; if (_TD.WORKDAYS == "") _TD.WORKDAYS = "0"; if (_TD.NOGOODDAYS == "") _TD.NOGOODDAYS = "0"; if (_TD.NODRVDAYS == "") _TD.NODRVDAYS = "0"; if (_TD.OTHERPAY == "") _TD.OTHERPAY = "0"; */ headList.Add(_TD); break; } } if (_success == false) { if (UnKnowenTruckNo == "") { UnKnowenTruckNo = " 有如下车辆不在月度报表中：" + TRUCKNO; } else { UnKnowenTruckNo += "," + TRUCKNO; } } } DbTransaction idbTran = connection.BeginTransaction(); var modb = new ModelObjectRepository(); DBResult result = modb.SaveComm("LINKGID", TMSRPT_GID, ModelObjectConvert.ToModelObjectList(headList), ModelObjectConvert.ToModelObjectList(DelList) ); isSucess = true; } catch (Exception exception) { //idbTran.Rollback(); isSucess = false; msg = exception.Message; } } UnKnowenTruckNo = UnKnowenTruckNo + " " + UnKnowenAirPort; return isSucess; } #endregion } }