using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.MvcShipping.Models.MsCtBankStatement; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.EntityDA; using DSWeb.Areas.CommMng.Models; using HcUtility.Comm; using System.Data.SqlClient; using DSWeb.DataAccess; using HcUtility.Core; using DSWeb.Areas.CommMng.DAL; using DSWeb.TruckMng.Comm.Cookie; using System.Data.Common; using System.Web; using DSWeb.Models; namespace DSWeb.MvcShipping.DAL.MsCtBankStatementDAL { public class MsCtBankStatementDAL { #region 查询主表 static public List GetDataList(string strCondition, string userid, string usercode, string companyid,string sort = null) { string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(userid); string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid); var strSql = new StringBuilder(); strSql.Append("SELECT *,CREATEUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_statement.CREATEUSER),MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_statement.MODIFIEDUSER),ACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] where STARTGID='" + strCwSTARTGID + "' and gid=ct_bank_statement.ACCGID),SETTLETYPENAME=(select top 1 STLNAME from [code_stlmode] where STLCODE=ct_bank_statement.SETTLETYPE),ISCHECKING=(case when CHECKINGGID='' then '' else (case when CHECKINGGID like 'auto%' then '*' else '**' end) end),ISIMPORTNAME=(case when ISIMPORT='True' then '是' else '' end) from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and ISINITIAL=0 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } // var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by VOUDATE,convert(decimal(10),ITEMNO)"); } return SetData(strSql); } static public MsCtBankStatement GetData(string condition, string companyid, string strUserID) { string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUserID); string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(strUserID); var strSql = new StringBuilder(); strSql.Append("SELECT *,CREATEUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_statement.CREATEUSER),MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_statement.MODIFIEDUSER),ACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] where STARTGID='" + strCwSTARTGID + "' and gid=ct_bank_statement.ACCGID),SETTLETYPENAME=(select top 1 STLNAME from [code_stlmode] where STLCODE=ct_bank_statement.SETTLETYPE),ISCHECKING=(case when CHECKINGGID='' then '' else (case when CHECKINGGID like 'auto%' then '*' else '**' end) end),ISIMPORTNAME=(case when ISIMPORT='True' then '是' else '' end) from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "'"); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" and " + condition); } var list=SetData(strSql); if (list.Count > 0) return list[0]; return new MsCtBankStatement(); } 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()) { MsCtBankStatement data = new MsCtBankStatement(); #region Set DB data to Object data.GID = (reader["GID"] == null ? "" : Convert.ToString(reader["GID"]));//惟一值 data.ACCGID = (reader["ACCGID"] == null ? "" : Convert.ToString(reader["ACCGID"]));//科目GID data.FCYNO = (reader["FCYNO"] == null ? "" : Convert.ToString(reader["FCYNO"]));//外币编号usd //data.VOUDATE = (reader["VOUDATE"] == null ? "" : Convert.ToString(reader["VOUDATE"]));//结算日期 if (reader["VOUDATE"] != null && reader["VOUDATE"].ToString().Trim().IndexOf("0001") < 0 && reader["VOUDATE"].ToString().Trim().IndexOf("1900") < 0 && reader["VOUDATE"].ToString().Trim() != "") { data.VOUDATE = Convert.ToDateTime(reader["VOUDATE"]);//结算日期 } data.ITEMNO = (reader["ITEMNO"] == null ? 0 : Convert.ToInt32(reader["ITEMNO"]));//当日序号 data.ACCDATE = (reader["ACCDATE"] == null ? "" : Convert.ToString(reader["ACCDATE"]));//会计期间 data.SETTLETYPE = (reader["SETTLETYPE"] == null ? 0 : Convert.ToInt32(reader["SETTLETYPE"]));//结算方式 data.BILLNO = (reader["BILLNO"] == null ? "" : Convert.ToString(reader["BILLNO"]));//结算号 data.DC = (reader["DC"] == null ? "" : Convert.ToString(reader["DC"]));//借贷方向 data.DR = (reader["DR"] == null ? 0 : Convert.ToDecimal(reader["DR"]));//原币借方金额(互斥) data.CR = (reader["CR"] == null ? 0 : Convert.ToDecimal(reader["CR"]));//原币贷方金额(互斥) data.FCYEXRATE = (reader["FCYEXRATE"] == null ? 0 : Convert.ToDecimal(reader["FCYEXRATE"]));//汇率 data.BLC = (reader["BLC"] == null ? 0 : Convert.ToDecimal(reader["BLC"]));//原币余额 data.EXPLAN = (reader["EXPLAN"] == null ? "" : Convert.ToString(reader["EXPLAN"]));//摘要，规则生成 data.ENTERED = (reader["ENTERED"] == null ? "" : Convert.ToString(reader["ENTERED"]));//经手人 data.ISINITIAL = (reader["ISINITIAL"] == null ? false : Convert.ToBoolean(reader["ISINITIAL"]));//是否期初 data.ISIMPORT = (reader["ISIMPORT"] == null ? false : Convert.ToBoolean(reader["ISIMPORT"]));//是否导入的数据 data.CHECKINGGID = (reader["CHECKINGGID"] == null ? "" : Convert.ToString(reader["CHECKINGGID"]));//对账GID data.STARTGID = (reader["STARTGID"] == null ? "" : Convert.ToString(reader["STARTGID"]));//账套启用GID data.CORPID = (reader["CORPID"] == null ? "" : Convert.ToString(reader["CORPID"]));//分公司GID data.CREATEUSER = (reader["CREATEUSER"] == null ? "" : Convert.ToString(reader["CREATEUSER"]));//制单人GID if (reader["CREATETIME"] != null && reader["CREATETIME"].ToString().Trim().IndexOf("0001") < 0 && reader["CREATETIME"].ToString().Trim().IndexOf("1900") < 0 && reader["CREATETIME"].ToString().Trim() != "") { data.CREATETIME = Convert.ToDateTime(reader["CREATETIME"]);//创建时间 } data.MODIFIEDUSER = (reader["MODIFIEDUSER"] == null ? "" : Convert.ToString(reader["MODIFIEDUSER"]));//最后一次更改操作人GID if (reader["MODIFIEDTIME"] != null && reader["MODIFIEDTIME"].ToString().Trim().IndexOf("0001") < 0 && reader["MODIFIEDTIME"].ToString().Trim().IndexOf("1900") < 0 && reader["MODIFIEDTIME"].ToString().Trim() != "") { data.MODIFIEDTIME = Convert.ToDateTime(reader["MODIFIEDTIME"]);//最后一次更改操作时间 } data.SETTLETYPENAME = (reader["SETTLETYPENAME"] == null ? "" : Convert.ToString(reader["SETTLETYPENAME"]));//结算方式 data.CREATEUSERNAME = (reader["CREATEUSERNAME"] == null ? "" : Convert.ToString(reader["CREATEUSERNAME"]));//制单人 data.MODIFIEDUSERNAME = (reader["MODIFIEDUSERNAME"] == null ? "" : Convert.ToString(reader["MODIFIEDUSERNAME"]));//最后一次更改操作人 data.ACCNAME = (reader["ACCNAME"] == null ? "" : Convert.ToString(reader["ACCNAME"]));//科目 data.ISCHECKING = (reader["ISCHECKING"] == null ? "" : Convert.ToString(reader["ISCHECKING"]));//对账状态 data.ISIMPORTNAME = (reader["ISIMPORTNAME"] == null ? "" : Convert.ToString(reader["ISIMPORTNAME"]));//是否导入的数据 #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 查询明细表 static public List GetDetailList(string strCondition, string userid, string usercode, string companyid) { T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(userid); string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid); // string sSQL = "select top 1 ACCDAY from ct_carry_over where STARTGID='" + strCtSTARTGID.Trim() + "' order by STARTGID,ACCDAY desc"; string sACCDAY = T_ALL_DA.GetStrSQL("ACCDAY", sSQL); if (sACCDAY != "") { sACCDAY = DateTime.Parse(sACCDAY).ToString("yyyy-MM-dd"); sACCDAY = " and VOUDATE>'" + sACCDAY + "'"; } // var strSql = new StringBuilder(); strSql.Append("SELECT *,CREATEUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_statement.CREATEUSER),MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_statement.MODIFIEDUSER),ACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] where STARTGID='" + strCwSTARTGID + "' and gid=ct_bank_statement.ACCGID),SETTLETYPENAME=(select top 1 STLNAME from [code_stlmode] where STLCODE=ct_bank_statement.SETTLETYPE),ISCHECKING=(case when CHECKINGGID='' then '' else (case when CHECKINGGID like 'auto%' then '*' else '**' end) end),ISIMPORTNAME=(case when ISIMPORT='True' then '是' else '' end) from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and (CHECKINGGID='' or CHECKINGGID is null)" + sACCDAY); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" and " + strCondition); } strSql.Append(" order by VOUDATE,convert(decimal(10),ITEMNO)"); return SetData(strSql); } #endregion #region 插入更新余额 public static void onBLC(MsCtBankStatement headRow, string companyid, string userid) { #region 默认值 string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid); DateTime _VOUDATE = headRow.VOUDATE; string sYear = _VOUDATE.Year.ToString(); string sMonth = _VOUDATE.Month.ToString(); string sDay = _VOUDATE.Day.ToString(); if (sMonth.Length == 1) { sMonth = "0" + sMonth; } if (sDay.Length == 1) { sDay = "0" + sDay; } T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string sSQL = "select DC from cw_accitems_gl with(nolock) where GID='" + headRow.ACCGID.ToString() + "'"; string ACCDC = T_ALL_DA.GetStrSQL("DC", sSQL); sSQL = "select top 1 GID,ACCGID,FCYNO,VOUDATE,'" + (sYear + "-" + sMonth + "-" + sDay) + "' as VOUDATE2,ITEMNO,'" + (sYear + "-" + sMonth) + "' as ACCDATE,SETTLETYPE,BILLNO,DC,DR=(case when DC='借' then BLC else 0 end),CR=(case when DC='贷' then BLC else 0 end),FCYEXRATE,BLC,EXPLAN,ENTERED,ISINITIAL,ISIMPORT,CHECKINGGID,STARTGID,CORPID,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME from ct_bank_statement with(nolock) where STARTGID='" + strCtSTARTGID + "' and convert(varchar,VOUDATE,23)<'" + (sYear + "-" + sMonth + "-" + sDay) + "' and ACCGID='" + headRow.ACCGID.ToString() + "' and FCYNO='" + headRow.FCYNO.ToString() + "' and ISINITIAL=0 order by VOUDATE desc";// and ISIMPORT=0 DataSet dsCtBankStatement = T_ALL_DA.GetAllSQL(sSQL); if (dsCtBankStatement == null) { sSQL = "select top 1 GID,ACCGID,CURRENCY as FCYNO,'" + (sYear + "-" + sMonth + "-" + sDay) + "' as VOUDATE,'" + (sYear + "-" + sMonth + "-" + sDay) + "' as VOUDATE2,0 as ITEMNO,'" + (sYear + "-" + sMonth) + "' as ACCDATE,'' as SETTLETYPE,'' as BILLNO,DC=(case when QTYYEARBLC_B<0 then '借' else '贷' end),DR=isnull((case when QTYYEARBLC_B<0 then -QTYYEARBLC_B else 0 end),0),CR=isnull((case when QTYYEARBLC_B>0 then QTYYEARBLC_B else 0 end),0),1 as FCYEXRATE,BLC=0,'' as EXPLAN,'' as ENTERED,0 as ISINITIAL,0 as ISIMPORT,'' as CHECKINGGID,STARTGID,CORPID,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME from ct_initial_balance WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + headRow.ACCGID.ToString() + "' and CURRENCY='" + headRow.FCYNO.ToString() + "'"; dsCtBankStatement = T_ALL_DA.GetAllSQL(sSQL); } else { if (dsCtBankStatement.Tables[0].Rows.Count < 1) { sSQL = "select top 1 GID,ACCGID,CURRENCY as FCYNO,'" + (sYear + "-" + sMonth + "-" + sDay) + "' as VOUDATE,'" + (sYear + "-" + sMonth + "-" + sDay) + "' as VOUDATE2,0 as ITEMNO,'" + (sYear + "-" + sMonth) + "' as ACCDATE,'' as SETTLETYPE,'' as BILLNO,DC=(case when QTYYEARBLC_B<0 then '借' else '贷' end),DR=isnull((case when QTYYEARBLC_B<0 then -QTYYEARBLC_B else 0 end),0),CR=isnull((case when QTYYEARBLC_B>0 then QTYYEARBLC_B else 0 end),0),1 as FCYEXRATE,BLC=0,'' as EXPLAN,'' as ENTERED,0 as ISINITIAL,0 as ISIMPORT,'' as CHECKINGGID,STARTGID,CORPID,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME from ct_initial_balance WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + headRow.ACCGID.ToString() + "' and CURRENCY='" + headRow.FCYNO.ToString() + "'"; dsCtBankStatement = T_ALL_DA.GetAllSQL(sSQL); } } #endregion #region 更新余额 Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { #region 循环更新余额 sSQL = "update [ct_bank_statement] set BLC=(" + "select (case when '" + headRow.FCYNO.ToString() + "'='RMB' then (case when '" + ACCDC + "'='借' then (isnull(sum(CR),0)-isnull(sum(DR),0)) else (isnull(sum(DR),0)-isnull(sum(CR),0)) end) else 0 end)" + " FROM (" + "select top 1 GID,'" + headRow.ACCGID.ToString() + "' as ACCGID,'" + headRow.FCYNO.ToString() + "' as CURRENCY,'" + dsCtBankStatement.Tables[0].Rows[0]["VOUDATE2"].ToString() + "' as VOUDATE,0 as ITEMNO,'" + dsCtBankStatement.Tables[0].Rows[0]["ACCDATE"].ToString() + "' as ACCDATE,'' as SETTLETYPE,'' as BILLNO" + ",DC='" + dsCtBankStatement.Tables[0].Rows[0]["DC"].ToString() + "'" + ",DR=" + dsCtBankStatement.Tables[0].Rows[0]["DR"].ToString() + ",CR=" + dsCtBankStatement.Tables[0].Rows[0]["CR"].ToString() + ",1 as FCYEXRATE,BLC=0" + ",'' as EXPLAN,'' as ENTERED,0 as ISINITIAL,0 as ISIMPORT,'' as CHECKINGGID,STARTGID,CORPID,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME " + " from ct_initial_balance WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "'" + " union " + "select GID,ACCGID,FCYNO,VOUDATE,ITEMNO,ACCDATE,SETTLETYPE,BILLNO,DC,DR,CR,FCYEXRATE,BLC,EXPLAN,ENTERED,ISINITIAL,ISIMPORT,CHECKINGGID,STARTGID,CORPID,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and convert(varchar,VOUDATE,23)>='" + (sYear + "-" + sMonth + "-" + sDay) + "' and ACCGID='" + headRow.ACCGID.ToString() + "' and FCYNO='" + headRow.FCYNO.ToString() + "'"// and ISIMPORT=0 + ") as a where a.STARTGID='" + strCtSTARTGID + "' and ACCGID='" + headRow.ACCGID.ToString() + "' and FCYNO='" + headRow.FCYNO.ToString() + "' and a.ITEMNO<=ct_bank_statement.ITEMNO and a.VOUDATE<=ct_bank_statement.VOUDATE" + ") where STARTGID='" + strCtSTARTGID + "' and convert(varchar,VOUDATE,23)>='" + (sYear + "-" + sMonth + "-" + sDay) + "' and ACCGID='" + headRow.ACCGID.ToString() + "' and FCYNO='" + headRow.FCYNO.ToString() + "' and ISINITIAL=0";// and ISIMPORT=0 #endregion var cmdSql = db.GetSqlStringCommand(sSQL); db.ExecuteNonQuery(cmdSql, tran); // tran.Commit(); } catch (Exception) { tran.Rollback(); } } #endregion #region 根据科目方向更新余额和余额方向 Database db2 = DatabaseFactory.CreateDatabase(); using (var conn = db2.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { #region 循环更新余额 sSQL = "update [ct_bank_statement] set " + "DC=(select (case when '" + ACCDC + "'='借' then (case when BLC>=0 then '贷' else '借' end) else (case when BLC>=0 then '借' else '贷' end) end) as DC FROM ct_bank_statement as a where a.STARTGID='" + strCtSTARTGID + "' and a.GID=ct_bank_statement.GID)" + ",BLC=(select (case when BLC>=0 then BLC else -BLC end) as BLC FROM ct_bank_statement as b where b.STARTGID='" + strCtSTARTGID + "' and b.GID=ct_bank_statement.GID)" + " where STARTGID='" + strCtSTARTGID + "' and convert(varchar,VOUDATE,23)='" + (sYear + "-" + sMonth + "-" + sDay) + "' and ACCGID='" + headRow.ACCGID.ToString() + "' and FCYNO='" + headRow.FCYNO.ToString() + "' and ISINITIAL=0";// and ISIMPORT=0 #endregion var cmdSql = db2.GetSqlStringCommand(sSQL); db2.ExecuteNonQuery(cmdSql, tran); // tran.Commit(); } catch (Exception) { tran.Rollback(); } } #endregion } #endregion #region 更新期初余额 public static void onINITIAL(MsCtBankStatement headRow, string companyid, string userid) { #region 默认值 string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid); T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); #endregion #region 更新期初余额 Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { #region 更新期初日记账 string sSQL = "update [ct_initial_balance] set QTYYEARCR_D=(select isnull(sum(CR),0) as CR from ct_bank_statement where ISINITIAL=1 and STARTGID='" + strCtSTARTGID + "' and ACCGID='" + headRow.ACCGID.ToString().Trim() + "' and FCYNO='" + headRow.FCYNO.ToString().Trim() + "'),QTYYEARDR_D=(select isnull(sum(DR),0) as DR from ct_bank_statement where ISINITIAL=1 and STARTGID='" + strCtSTARTGID + "' and ACCGID='" + headRow.ACCGID.ToString().Trim() + "' and FCYNO='" + headRow.FCYNO.ToString().Trim() + "') where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + headRow.ACCGID.ToString().Trim() + "' and CURRENCY='" + headRow.FCYNO.ToString().Trim() + "'"; var cmdSql = db.GetSqlStringCommand(sSQL); db.ExecuteNonQuery(cmdSql, tran); // sSQL = "update [ct_initial_balance] set QTYYEARBLC_DH=isnull((QTYYEARBLC_D+QTYYEARCR_D-QTYYEARDR_D),0) where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + headRow.ACCGID.ToString().Trim() + "' and CURRENCY='" + headRow.FCYNO.ToString().Trim() + "'"; cmdSql = db.GetSqlStringCommand(sSQL); db.ExecuteNonQuery(cmdSql, tran); #endregion // tran.Commit(); } catch (Exception) { tran.Rollback(); } } #endregion } #endregion #region 删除 public static DBResult Delete(string gids, string companyid, string userid) { #region 默认值 T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(userid); string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid); var result = new DBResult(); string sError = ""; //var sGids = " and GID in ('" + gids.Replace(",", "','") + "')"; string[] gidList = gids.Trim().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); #endregion for (int i = 0; i < gidList.Length; i++) { string sGids = " and GID in ('" + gidList[i].ToString() + "')"; #region 判断是否符合删除条件 string sSQL = "select count(*) as inum from ct_bank_statement where STARTGID='" + strCtSTARTGID + "' and (CHECKINGGID<>'' and CHECKINGGID is not null)" + sGids; int inum = int.Parse(T_ALL_DA.GetStrSQL("inum", sSQL)); if (inum > 0) { //result.Success = false; //result.Message = "操作错误，已经对账的数据，不允许操作!";//或轧账 //return result; sError = "已经对账的数据，不允许操作!"; continue; } #endregion #region 删除 Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand("delete from ct_bank_statement where STARTGID='" + strCtSTARTGID + "'" + sGids); db.ExecuteNonQuery(cmdDelete, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); //result.Success = false; //result.Message = "删除出现错误，请重试!"; //return result; continue; } } #endregion #region 更新其他信息 var headList = new List(); var strSql = new StringBuilder(); strSql.Append("SELECT *,CREATEUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_statement.CREATEUSER),MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_statement.MODIFIEDUSER),ACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] where STARTGID='" + strCwSTARTGID + "' and gid=ct_bank_statement.ACCGID),SETTLETYPENAME=(select top 1 STLNAME from [code_stlmode] where STLCODE=ct_bank_statement.SETTLETYPE),ISCHECKING=(case when CHECKINGGID='' then '' else (case when CHECKINGGID like 'auto%' then '*' else '**' end) end),ISIMPORTNAME=(case when ISIMPORT='True' then '是' else '' end) from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and (CHECKINGGID='' or CHECKINGGID is null)" + sGids); strSql.Append(" order by VOUDATE,convert(decimal(10),ITEMNO)"); headList = SetData(strSql); foreach (MsCtBankStatement headRow in headList) { //MsCtBankStatement headRow = new MsCtBankStatement(); //插入更新余额 onBLC(headRow, companyid, userid); } #endregion } result.Success = true; result.Message = "操作完成！" + sError; return result; } public static void DeleteDetail(MsCtBankStatement headRow, string companyid, string userid) { string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid); var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand("delete from ct_bank_statement where STARTGID='" + strCtSTARTGID + "' and GID='" + headRow.GID.ToString() + "'"); db.ExecuteNonQuery(cmdDelete, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); } } } #endregion #region 导入Excel银行流水单 /// /// 导入Excel银行流水单 /// /// 对账银行配置表 /// 导入的excel内容 /// 新增条数 /// 失败(更新)条数 /// 失败提示信息 /// 执行条数 /// public static bool ImportExcelData(string strACCGID, string strFCYNO, DataSet dsCtBankDeploy, DataTable table, out int InsertCount, out int UpdateCount, out string UnKnowenTruckNo, out int iheadList, string userid, string companyid) { #region 默认值 DBResult result = new DBResult(); if (table == null) throw new ArgumentNullException("table"); InsertCount = 0; UpdateCount = 0; UnKnowenTruckNo = ""; iheadList = 0; T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string sSQL = "select DC from cw_accitems_gl With(NoLock) where GID='" + strACCGID + "'"; string ACCDC = T_ALL_DA.GetStrSQL("DC", sSQL); #endregion string sVOUDATE = ""; string _EXPLAN = ""; Decimal _BLC = 0; foreach (DataRow row in table.Rows) { //Database db = DatabaseFactory.CreateDatabase(); //using (var conn = db.CreateConnection()) //{ // conn.Open(); // var tran = conn.BeginTransaction(); try { sVOUDATE = row[dsCtBankDeploy.Tables[0].Rows[0]["VOUDATE"].ToString()].ToString().Trim(); if (sVOUDATE.Trim().Length == 8) { sVOUDATE = sVOUDATE.Substring(0, 4) + "-" + sVOUDATE.Substring(4, 2) + "-" + sVOUDATE.Substring(6, 2); } if (sVOUDATE != "") { #region 规范取值 DateTime _VOUDATE = Convert.ToDateTime(sVOUDATE); string sYear = _VOUDATE.Year.ToString(); string sMonth = _VOUDATE.Month.ToString(); string sDay = _VOUDATE.Day.ToString(); if (sMonth.Length == 1) { sMonth = "0" + sMonth; } if (sDay.Length == 1) { sDay = "0" + sDay; } if (dsCtBankDeploy.Tables[0].Rows[0]["VOUTIME"].ToString().Trim() != "") { string sVOUTIME = row[dsCtBankDeploy.Tables[0].Rows[0]["VOUTIME"].ToString()].ToString(); if (sVOUTIME.Trim().Length == 6) { sVOUTIME = sVOUTIME.Substring(0, 2) + ":" + sVOUTIME.Substring(2, 2) + ":" + sVOUTIME.Substring(4, 2); } _VOUDATE = Convert.ToDateTime(sVOUDATE.Replace(" 00:00:00", "").Trim() + " " + sVOUTIME); } // _EXPLAN = row[dsCtBankDeploy.Tables[0].Rows[0]["EXPLAN"].ToString()].ToString(); // Decimal _DR = 0; Decimal _CR = 0; if (dsCtBankDeploy.Tables[0].Rows[0]["DR"].ToString() == dsCtBankDeploy.Tables[0].Rows[0]["CR"].ToString()) { string sDC = row[dsCtBankDeploy.Tables[0].Rows[0]["DC"].ToString()].ToString(); string sDRGJZ = dsCtBankDeploy.Tables[0].Rows[0]["DRGJZ"].ToString(); string sCRGJZ = dsCtBankDeploy.Tables[0].Rows[0]["CRGJZ"].ToString(); if (sDC.IndexOf(sDRGJZ) > -1) { _DR = row[dsCtBankDeploy.Tables[0].Rows[0]["DR"].ToString()].ToString().Replace("--", "0") == "" ? 0 : Convert.ToDecimal(row[dsCtBankDeploy.Tables[0].Rows[0]["DR"].ToString()].ToString().Replace("--", "0")); if (_DR < 0) { _DR = _DR * (-1); } _CR = 0; } if (sDC.IndexOf(sCRGJZ) > -1) { _DR = 0; _CR = row[dsCtBankDeploy.Tables[0].Rows[0]["CR"].ToString()].ToString().Replace("--", "0") == "" ? 0 : Convert.ToDecimal(row[dsCtBankDeploy.Tables[0].Rows[0]["CR"].ToString()].ToString().Replace("--", "0")); if (_CR < 0) { _CR = _CR * (-1); } } } else { _DR = row[dsCtBankDeploy.Tables[0].Rows[0]["DR"].ToString()].ToString().Replace("--", "0") == "" ? 0 : Convert.ToDecimal(row[dsCtBankDeploy.Tables[0].Rows[0]["DR"].ToString()].ToString().Replace("--", "0")); _CR = row[dsCtBankDeploy.Tables[0].Rows[0]["CR"].ToString()].ToString().Replace("--", "0") == "" ? 0 : Convert.ToDecimal(row[dsCtBankDeploy.Tables[0].Rows[0]["CR"].ToString()].ToString().Replace("--", "0")); } // _BLC = row[dsCtBankDeploy.Tables[0].Rows[0]["BLC"].ToString()].ToString().Replace("--", "0") == "" ? 0 : Convert.ToDecimal(row[dsCtBankDeploy.Tables[0].Rows[0]["BLC"].ToString()].ToString().Replace("--", "0")); string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid); string strACCDATE = sYear + "-" + sMonth; #endregion string sGID = ""; //sSQL = "select top 1 GID from ct_bank_statement With(NoLock) where VOUDATE='" + _VOUDATE.ToString("yyyy-MM-dd HH:mm:ss") + "' and EXPLAN='" + _EXPLAN + "' and DR='" + _DR + "' and CR='" + _CR + "' and BLC='" + _BLC + "' and STARTGID='" + strCtSTARTGID + "' and ACCDATE='" + strACCDATE + "'"; //string sGID = T_ALL_DA.GetStrSQL("GID", sSQL); if (sGID == "")//判断是否是重复数据 { MsCtBankStatement headRow = new MsCtBankStatement(); #region 默认值 headRow.GID = Guid.NewGuid().ToString(); headRow.ACCGID = strACCGID;//科目GID headRow.FCYNO = strFCYNO;//外币编号usd headRow.VOUDATE = _VOUDATE;//结算日期 sSQL = "select isnull((select MAX(convert(decimal(10),ISNULL(ITEMNO,0))) as iITEMNO from ct_bank_statement where STARTGID='" + strCtSTARTGID + "' and convert(varchar,VOUDATE,23)='" + (sYear + "-" + sMonth + "-" + sDay) + "' ),0) as iITEMNO"; int iITEMNO = int.Parse(T_ALL_DA.GetStrSQL("iITEMNO", sSQL)); headRow.ITEMNO = iITEMNO + 1;//当日序号 headRow.ACCDATE = strACCDATE;//会计期间 headRow.SETTLETYPE = 0;//结算方式 headRow.BILLNO = "";//结算号 if (_DR != 0) { if (ACCDC == "借") { headRow.DC = ACCDC;//余额方向 } else { headRow.DC = "贷";//余额方向 } } else { if (ACCDC == "借") { headRow.DC = "贷";//余额方向 } else { headRow.DC = ACCDC;//余额方向 } } headRow.DR = _DR;//原币借方金额(互斥) headRow.CR = _CR;//原币贷方金额(互斥) headRow.FCYEXRATE = 1;//汇率 headRow.BLC = _BLC;//原币余额 headRow.EXPLAN = _EXPLAN;//摘要，规则生成 headRow.ENTERED = "";//经手人 headRow.ISINITIAL = false;//是否期初 headRow.ISIMPORT = true;//是否导入的数据 headRow.CHECKINGGID = "";//对账GID headRow.STARTGID = strCtSTARTGID;//账套启用GID headRow.CORPID = companyid;//分公司GID headRow.CREATEUSER = userid;//制单人GID headRow.CREATETIME = DateTime.Now;//创建时间 headRow.MODIFIEDUSER = userid;//最后一次更改操作人GID headRow.MODIFIEDTIME = DateTime.Now;//最后一次更改操作时间 headRow.DbOperationType = DbOperationType.DbotIns; #endregion var modb = new ModelObjectDB(); result = modb.Save(headRow); if (!result.Success) { UnKnowenTruckNo += result.Message; } InsertCount++; } else { //数据重复 UpdateCount++; UnKnowenTruckNo +="("+ _VOUDATE+"," +_EXPLAN+","+ _BLC.ToString()+";"; } iheadList++; } } catch (Exception e) { iheadList++; UpdateCount++; result.Success = true; UnKnowenTruckNo += "(" + sVOUDATE + "," + _EXPLAN + "," + _BLC.ToString() + ";"+ e.Message; continue; //tran.Rollback(); } // } } return result.Success; } #endregion } }