using System; using System.Data; using System.Data.Common; using System.Collections.Generic; using System.Text; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.Areas.CommMng.Models; using System.Web; using System.IO; using System.Data.SqlClient; using WebSqlHelper; namespace DSWeb.Areas.TruckMng.DAL.MsWlDriver { public partial class MsWlDriverDAL { #region Inquery DataList 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 "); strSql.Append("ROW_NUMBER() OVER (ORDER BY OrgCode) AS RowNumber,OrgCode,DrvCode,DrvName,DrvClass,"); strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99017 and EnumValueID=tMsWlDriver.DrvClass) as DrvClass_Ref"); strSql.Append(",JzNo,LzDate,LimitDays,DriverYears,TruckType,"); strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99019 and EnumValueID=tMsWlDriver.TruckType) as TruckType_Ref"); strSql.Append(",Licence,YearCheckDate,NextCheckDate,NoticeDays,Mobile,OffTel,Remark,CertificateNo,Addr,GID,BASESALARY"); strSql.Append(" from tMsWlDriver "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } return SetData(strSql); } static public Models.MsWlDriver 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 Models.MsWlDriver(); } 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()) { Models.MsWlDriver data = new Models.MsWlDriver(); #region Set DB data to Object data.RowNumber = Convert.ToString(reader["RowNumber"]); data.GID = Convert.ToString(reader["GID"]); data.OrgCode = Convert.ToString(reader["OrgCode"]); data.DrvCode = Convert.ToString(reader["DrvCode"]); data.DrvName = Convert.ToString(reader["DrvName"]); data.DrvClass = Convert.ToString(reader["DrvClass"]); data.DrvClass_Ref = Convert.ToString(reader["DrvClass_Ref"]); data.JzNo = Convert.ToString(reader["JzNo"]); data.LzDate = Convert.ToString(reader["LzDate"]); data.LimitDays = Convert.ToDecimal(reader["LimitDays"]); data.DriverYears = Convert.ToDecimal(reader["DriverYears"]); data.TruckType = Convert.ToString(reader["TruckType"]); data.TruckType_Ref = Convert.ToString(reader["TruckType_Ref"]); data.Licence = Convert.ToString(reader["Licence"]); data.YearCheckDate = Convert.ToString(reader["YearCheckDate"]); data.NextCheckDate = Convert.ToString(reader["NextCheckDate"]); data.NoticeDays = Convert.ToDecimal(reader["NoticeDays"]); data.Mobile = Convert.ToString(reader["Mobile"]); data.OffTel = Convert.ToString(reader["OffTel"]); data.Remark = Convert.ToString(reader["Remark"]); data.CertificateNo = Convert.ToString(reader["CertificateNo"]); data.Addr = Convert.ToString(reader["Addr"]); data.BASESALARY = Convert.ToString(reader["BASESALARY"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region FileList static public List GetUpLoadFileList(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("GID,PID,FILENAME,FILETYPE,IMGFILE,UPLOADEMPLY,UPLOADDATE,FILEPATH"); strSql.Append(" from info_files "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } return SetUpLoadFileData(strSql); } private static List SetUpLoadFileData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { Models.MsUpLoadFile data = new Models.MsUpLoadFile(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.PID = Convert.ToString(reader["PID"]); data.UPLOADEMPLY = Convert.ToString(reader["UPLOADEMPLY"]); data.UPLOADDATE = Convert.ToString(reader["UPLOADDATE"]); data.FILETYPE = Convert.ToString(reader["FILETYPE"]); data.FILENAME = Convert.ToString(reader["FILENAME"]); data.FILEPATH = Convert.ToString(reader["FILEPATH"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion public static bool UpdateFileData(HttpRequestBase request, string filename,string GID, out string msg) { var isSucess = false; msg = ""; Stream stream = new FileStream(filename, FileMode.Open); byte[] data = new byte[stream.Length]; stream.Read(data, 0, data.Length); stream.Close(); using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { const string insertSql = @"update info_files set IMGFILE=@Image,FILEPATH=@FILEPATH where GID=@GID"; SqlParameter[] updateParms = new SqlParameter[] { new SqlParameter("@GID",SqlDbType.VarChar), new SqlParameter("@Image",SqlDbType.Image), new SqlParameter("@FILEPATH",SqlDbType.VarChar) }; updateParms[0].Value = GID; updateParms[1].Value = data; updateParms[2].Value = filename; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, insertSql, updateParms); sqlTran.Commit(); isSucess = true;//状态为1表示插入成功 } catch (Exception execError) { isSucess = false;//有异常,插入失败 sqlTran.Rollback(); msg = execError.Message; } finally { SqlHelper.CloseConnection(); } } return isSucess; } static public int SaveFileList(IList tempFileEntities, string tempBSNO) { int iResult = 0; const string SQL_INSERT = @"insert info_files (GID,PID,UPLOADEMPLY,UPLOADDATE,FILENAME,FILETYPE) VALUES(@GID,@PID,@UPLOADEMPLY,@UPLOADDATE,@FILENAME,@FILETYPE) "; const string SQL_UPDATE = @"update info_files SET FILENAME=@FILENAME where GID=@GID"; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { foreach (Models.MsUpLoadFile fileEntity in tempFileEntities) { if (fileEntity.PID == "*" || fileEntity.PID == "") { SqlParameter[] insertParms = new SqlParameter[] { new SqlParameter("@GID",SqlDbType.VarChar), new SqlParameter("@PID",SqlDbType.VarChar), new SqlParameter("@UPLOADEMPLY",SqlDbType.VarChar), new SqlParameter("@UPLOADDATE",SqlDbType.VarChar), new SqlParameter("@FILENAME",SqlDbType.VarChar), new SqlParameter("@FILETYPE",SqlDbType.VarChar) }; insertParms[0].Value = fileEntity.GID; insertParms[1].Value = tempBSNO; insertParms[2].Value = fileEntity.UPLOADEMPLY; insertParms[3].Value = fileEntity.UPLOADDATE; insertParms[4].Value = fileEntity.FILENAME; insertParms[5].Value = fileEntity.FILETYPE; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT, insertParms); } else { SqlParameter[] updateParms = new SqlParameter[] { new SqlParameter("@FILENAME",SqlDbType.VarChar), new SqlParameter("@GID",SqlDbType.VarChar) }; //updateParms[0].Value = fileEntity.GID; //updateParms[1].Value = tempBSNO; //updateParms[2].Value = fileEntity.UPLOADEMPLY; //updateParms[3].Value = fileEntity.UPLOADDATE; updateParms[0].Value = fileEntity.FILENAME; updateParms[1].Value = fileEntity.GID; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE, updateParms); } } sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } static public int DeleteUploadFile(string tempGId) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append("Delete from info_files where "); strSql.Append(" GId in (" + tempGId + ")"); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #region 参照部分 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 } public partial class MsWlDriver_SalaryDAL { #region Inquery DataList 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,DRVCODE,ACCDATE,dbo.trimdate(EXPDATEBGN) EXPDATEBGN "); strSql.Append(" ,dbo.trimdate(EXPDATEEND) EXPDATEEND,BASESALARY,BONUS,RKF,AMOUNT,AMOUNT_0 "); strSql.Append(" ,dbo.trimdate(PAYDATE) PAYDATE,SETTLEMENT,REMARK,ISLOCK "); strSql.Append(" ,(select Drvname from tmswldriver where drvcode=tMsWlDriver_Salary.drvcode) DRVNAME "); strSql.Append(" ,(case when ISLOCK=0 then '未发' when (settlement>0 and settlement=amount and ISLOCK=1) then '已发' end) STLSTATUS "); strSql.Append(" from tMsWlDriver_Salary "); 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 ACCDATE,DRVCODE "); } return SetData(strSql); } static public Models.MsWlDriver_Salary 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 Models.MsWlDriver_Salary(); } 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()) { Models.MsWlDriver_Salary data = new Models.MsWlDriver_Salary(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.DRVCODE = Convert.ToString(reader["DRVCODE"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.EXPDATEBGN = Convert.ToString(reader["EXPDATEBGN"]); data.EXPDATEEND = Convert.ToString(reader["EXPDATEEND"]); data.BASESALARY = Convert.ToString(reader["BASESALARY"]); data.BONUS = Convert.ToString(reader["BONUS"]); data.RKF = Convert.ToString(reader["RKF"]); data.AMOUNT = Convert.ToString(reader["AMOUNT"]); data.AMOUNT_0 = Convert.ToString(reader["AMOUNT_0"]); data.PAYDATE = Convert.ToString(reader["PAYDATE"]); data.SETTLEMENT = Convert.ToString(reader["SETTLEMENT"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.ISLOCK = Convert.ToString(reader["ISLOCK"]); data.DRVNAME = Convert.ToString(reader["DRVNAME"]); data.STLSTATUS = Convert.ToString(reader["STLSTATUS"]); #endregion headList.Add(data); } reader.Close(); } return headList; } static public List GetSalary ( string DrvCode, string EXPDATEBGN, string EXPDATEEND ) { var strSql = new StringBuilder(); strSql.Append(" select DrvCode,(select isnull(basesalary,0) from tMsWlDriver where DrvCode=tMsWlPcHead.DrvCode) BASESALARY "); strSql.Append(" , sum(dbo.get_Fee(GID,'包干费',1)*0.89-dbo.get_Fee(GID,'背箱费',2)) AMOUNT_0 from tMsWlPcHead "); strSql.Append(" where DrvCode in(" + DrvCode + ") "); strSql.Append(" and ExpDate between '" + EXPDATEBGN + "' and '" + EXPDATEEND + "' "); strSql.Append(" group by drvcode "); return SetSalaryData(strSql); } static public List GetSalary ( string DrvCode, string EXPDATEBGN, string EXPDATEEND ,string DRVCODE) { var strSql = new StringBuilder(); strSql.Append(" select DrvCode,(select isnull(basesalary,0) from tMsWlDriver where DrvCode=tMsWlPcHead.DrvCode) BASESALARY "); strSql.Append(" , sum(dbo.get_Fee(GID,'包干费',1)*0.89-dbo.get_Fee(GID,'背箱费',2)) AMOUNT_0 from tMsWlPcHead "); strSql.Append(" where DrvCode in(" + DrvCode + ") "); strSql.Append(" and ExpDate between '" + EXPDATEBGN + "' and '" + EXPDATEEND + "' and DRVCODE='" + DRVCODE + "'"); strSql.Append(" group by drvcode "); return SetSalaryData(strSql); } private static List SetSalaryData ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { Models.SalaryDetail data = new Models.SalaryDetail(); #region Set DB data to Object data.DRVCODE = Convert.ToString(reader["DRVCODE"]); data.BASESALARY = Convert.ToString(reader["BASESALARY"]); data.AMOUNT_0 = Convert.ToString(reader["AMOUNT_0"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion static public int Delete ( string tempGId ) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append("Delete from tmswlDriver_Salary where "); strSql.Append(" GId in (" + tempGId + ")"); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #region 参照部分 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 } }