You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
609 lines
40 KiB
C#
609 lines
40 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using DSWeb.MvcShipping.Models.MsCtBankJournal;
|
|
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;
|
|
|
|
namespace DSWeb.MvcShipping.DAL.MsCtBankCheckingDAL
|
|
{
|
|
public class MsCtBankCheckingDAL
|
|
{
|
|
#region 查询_银行日记账
|
|
static public List<MsCtBankJournal> 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_journal.CREATEUSER),MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_journal.MODIFIEDUSER),ACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] where STARTGID='" + strCwSTARTGID + "' and gid=ct_bank_journal.ACCGID),SETTLETYPENAME=(select top 1 STLNAME from [code_stlmode] where STLCODE=ct_bank_journal.SETTLETYPE),ISCHECKING=(case when CHECKINGGID='' then '' else (case when CHECKINGGID like 'auto%' then '*' else '**' end) end),VOUDATE_C=(select top 1 VOUDATE from [ct_bank_checking] where STARTGID='" + strCtSTARTGID + "' and GID=ct_bank_journal.CHECKINGGID) from ct_bank_journal WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "'");
|
|
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 MsCtBankJournal GetData(string condition, string companyid, string userid)
|
|
{
|
|
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_journal.CREATEUSER),MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=ct_bank_journal.MODIFIEDUSER),ACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] where STARTGID='" + strCwSTARTGID + "' and gid=ct_bank_journal.ACCGID),SETTLETYPENAME=(select top 1 STLNAME from [code_stlmode] where STLCODE=ct_bank_journal.SETTLETYPE),ISCHECKING=(case when CHECKINGGID='' then '' else (case when CHECKINGGID like 'auto%' then '*' else '**' end) end),VOUDATE_C=(select top 1 VOUDATE from [ct_bank_checking] where STARTGID='" + strCtSTARTGID + "' and GID=ct_bank_journal.CHECKINGGID) from ct_bank_journal 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 MsCtBankJournal();
|
|
}
|
|
|
|
private static List<MsCtBankJournal> SetData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<MsCtBankJournal>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsCtBankJournal data = new MsCtBankJournal();
|
|
#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.VGID = (reader["VGID"] == null ? "" : Convert.ToString(reader["VGID"]));//凭证明细GID
|
|
data.VKNO = (reader["VKNO"] == null ? "" : Convert.ToString(reader["VKNO"]));//凭证字
|
|
data.VOUNO = (reader["VOUNO"] == null ? "" : Convert.ToString(reader["VOUNO"]));//凭证号
|
|
data.FENTRYID = (reader["FENTRYID"] == null ? 0 : Convert.ToInt32(reader["FENTRYID"]));//分录号
|
|
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.SVOUDATE = (reader["SVOUDATE"] == null ? "" : Convert.ToString(reader["SVOUDATE"]));//结算日期
|
|
if (reader["SVOUDATE"] != null && reader["SVOUDATE"].ToString().Trim().IndexOf("0001") < 0 && reader["SVOUDATE"].ToString().Trim().IndexOf("1900") < 0 && reader["SVOUDATE"].ToString().Trim() != "")
|
|
{
|
|
data.SVOUDATE = Convert.ToDateTime(reader["SVOUDATE"]);//结算日期
|
|
}
|
|
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.QTYDR = (reader["QTYDR"] == null ? 0 : Convert.ToDecimal(reader["QTYDR"]));//折合本位币借方
|
|
data.QTYCR = (reader["QTYCR"] == null ? 0 : Convert.ToDecimal(reader["QTYCR"]));//折合本位币贷方
|
|
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.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.VOUDATE_C = (reader["VOUDATE_C"] == null ? "" : Convert.ToString(reader["VOUDATE_C"]));//对账日期
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 查询_银行对账单
|
|
static public List<MsCtBankStatement> GetDataListStatement(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),VOUDATE_C=(select top 1 VOUDATE from [ct_bank_checking] where STARTGID='" + strCtSTARTGID + "' and GID=ct_bank_statement.CHECKINGGID) from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "'");
|
|
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 SetDataStatement(strSql);
|
|
}
|
|
|
|
static public MsCtBankStatement GetDataStatement(string condition, string companyid, string userid)
|
|
{
|
|
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),VOUDATE_C=(select top 1 VOUDATE from [ct_bank_checking] where STARTGID='" + strCtSTARTGID + "' and GID=ct_bank_statement.CHECKINGGID) from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "'");
|
|
if (!string.IsNullOrEmpty(condition))
|
|
{
|
|
strSql.Append(" and " + condition);
|
|
}
|
|
var list = SetDataStatement(strSql);
|
|
if (list.Count > 0)
|
|
return list[0];
|
|
return new MsCtBankStatement();
|
|
}
|
|
|
|
private static List<MsCtBankStatement> SetDataStatement(StringBuilder strSql)
|
|
{
|
|
var headList = new List<MsCtBankStatement>();
|
|
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.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"]));//是否导入的数据
|
|
data.VOUDATE_C = (reader["VOUDATE_C"] == null ? "" : Convert.ToString(reader["VOUDATE_C"]));//对账日期
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region 自动对账
|
|
public static DBResult onAutoClick(string sACCGID, string sVOUDATE, string sVOUDATEend, string sDaysOf, string sCbSETTLETYPE, string sCbBILLNO, string sCbVOUDATE, string companyid, string userid)
|
|
{
|
|
#region 默认值
|
|
var result = new DBResult();
|
|
var headList = new List<MsCtBankJournal>();
|
|
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
|
|
string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid);
|
|
int iDZS = 0;
|
|
#endregion
|
|
|
|
#region 已银行对账单为准
|
|
#region 借
|
|
string sSql = "SELECT * from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and (CHECKINGGID is null or CHECKINGGID='') and DR<>0 and ACCGID='" + sACCGID + "' and convert(varchar,VOUDATE,23)<='" + sVOUDATEend + "'";
|
|
DataSet ds = T_ALL_DA.GetAllSQL(sSql);//银行对账单
|
|
if (ds != null)
|
|
{
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
|
{
|
|
#region 银行日记账匹配条件
|
|
string strSQL = "select top 1 GID from ct_bank_journal WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and (CHECKINGGID is null or CHECKINGGID='') and CR<>0 and CR=" + ds.Tables[0].Rows[i]["DR"].ToString() + " and ACCGID='" + sACCGID + "' and FCYNO='" + ds.Tables[0].Rows[i]["FCYNO"].ToString() + "' and convert(varchar,VOUDATE,23)<=convert(varchar(10),DateAdd(dd,(0),'" + sVOUDATEend + "'),23) and convert(varchar,VOUDATE,23)>=convert(varchar(10),DateAdd(dd,(-" + sDaysOf + "),'" + ds.Tables[0].Rows[i]["VOUDATE"].ToString() + "'),23) and convert(varchar,VOUDATE,23)<=convert(varchar(10),DateAdd(dd,(" + sDaysOf + "),'" + ds.Tables[0].Rows[i]["VOUDATE"].ToString() + "'),23)";
|
|
if (sCbSETTLETYPE.Trim() == "1")
|
|
{
|
|
strSQL += " and SETTLETYPE=" + ds.Tables[0].Rows[i]["SETTLETYPE"].ToString();
|
|
}
|
|
if (sCbBILLNO.Trim() == "1")
|
|
{
|
|
strSQL += " and BILLNO=" + ds.Tables[0].Rows[i]["BILLNO"].ToString();
|
|
}
|
|
if (sCbVOUDATE.Trim() == "1")
|
|
{
|
|
DateTime _VOUDATE = DateTime.Parse(ds.Tables[0].Rows[i]["VOUDATE"].ToString());
|
|
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;
|
|
}
|
|
strSQL += " and convert(varchar,sVOUDATE,23)='" + sYear + "-" + sMonth + "-" + sDay + "' and SETTLETYPE<>0";
|
|
}
|
|
#endregion
|
|
string sGID = T_ALL_DA.GetStrSQL("GID", strSQL);//银行日记账
|
|
if (sGID != "")
|
|
{
|
|
#region 更新插入对账单
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
try
|
|
{
|
|
string newGID = "auto" + Guid.NewGuid().ToString().Replace("-", "");
|
|
string lsSql = "INSERT INTO [ct_bank_checking]([GID],[LINKGID],[LINKTYPE],[VOUDATE],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME])"
|
|
+ " VALUES('" + newGID + "','" + ds.Tables[0].Rows[i]["GID"].ToString() + "','银行对账单','" + sVOUDATE + "','" + strCtSTARTGID + "','" + companyid + "','" + userid + "',getdate())";
|
|
var cmdSQL = db.GetSqlStringCommand(lsSql);
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
lsSql = "INSERT INTO [ct_bank_checking]([GID],[LINKGID],[LINKTYPE],[VOUDATE],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME])"
|
|
+ " VALUES('" + newGID + "','" + sGID + "','银行日记账','" + sVOUDATE + "','" + strCtSTARTGID + "','" + companyid + "','" + userid + "',getdate())";
|
|
cmdSQL = db.GetSqlStringCommand(lsSql);
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("update ct_bank_statement set CHECKINGGID='" + newGID + "' where STARTGID='" + strCtSTARTGID + "' and GID='" + ds.Tables[0].Rows[i]["GID"].ToString() + "'");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("update ct_bank_journal set CHECKINGGID='" + newGID + "' where STARTGID='" + strCtSTARTGID + "' and GID='" + sGID + "'");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
tran.Commit();
|
|
iDZS++;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
}
|
|
}
|
|
#endregion
|
|
}
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 贷
|
|
sSql = "SELECT * from ct_bank_statement WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and (CHECKINGGID is null or CHECKINGGID='') and CR<>0 and ACCGID='" + sACCGID + "' and convert(varchar,VOUDATE,23)<='" + sVOUDATEend + "'";
|
|
ds = T_ALL_DA.GetAllSQL(sSql);
|
|
if (ds != null)
|
|
{
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
|
{
|
|
#region 银行日记账匹配条件
|
|
string strSQL = "select top 1 GID from ct_bank_journal WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and (CHECKINGGID is null or CHECKINGGID='') and DR<>0 and DR=" + ds.Tables[0].Rows[i]["CR"].ToString() + " and ACCGID='" + sACCGID + "' and FCYNO='" + ds.Tables[0].Rows[i]["FCYNO"].ToString() + "' and convert(varchar,VOUDATE,23)<=convert(varchar(10),DateAdd(dd,(0),'" + sVOUDATEend + "'),23) and convert(varchar,VOUDATE,23)>=convert(varchar(10),DateAdd(dd,(-" + sDaysOf + "),'" + ds.Tables[0].Rows[i]["VOUDATE"].ToString() + "'),23) and convert(varchar,VOUDATE,23)<=convert(varchar(10),DateAdd(dd,(" + sDaysOf + "),'" + ds.Tables[0].Rows[i]["VOUDATE"].ToString() + "'),23)";
|
|
if (sCbSETTLETYPE.Trim() == "1")
|
|
{
|
|
strSQL += " and SETTLETYPE=" + ds.Tables[0].Rows[i]["SETTLETYPE"].ToString();
|
|
}
|
|
if (sCbBILLNO.Trim() == "1")
|
|
{
|
|
strSQL += " and BILLNO=" + ds.Tables[0].Rows[i]["BILLNO"].ToString();
|
|
}
|
|
if (sCbVOUDATE.Trim() == "1")
|
|
{
|
|
DateTime _VOUDATE = DateTime.Parse(ds.Tables[0].Rows[i]["VOUDATE"].ToString());
|
|
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;
|
|
}
|
|
strSQL += " and convert(varchar,sVOUDATE,23)='" + sYear + "-" + sMonth + "-" + sDay + "' and SETTLETYPE<>0";
|
|
}
|
|
#endregion
|
|
string sGID = T_ALL_DA.GetStrSQL("GID", strSQL);
|
|
if (sGID != "")
|
|
{
|
|
#region 更新插入对账单
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
try
|
|
{
|
|
string newGID = "auto" + Guid.NewGuid().ToString().Replace("-", "");
|
|
string lsSql = "INSERT INTO [ct_bank_checking]([GID],[LINKGID],[LINKTYPE],[VOUDATE],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME])"
|
|
+ " VALUES('" + newGID + "','" + ds.Tables[0].Rows[i]["GID"].ToString() + "','银行对账单','" + sVOUDATE + "','" + strCtSTARTGID + "','" + companyid + "','" + userid + "',getdate())";
|
|
var cmdSQL = db.GetSqlStringCommand(lsSql);
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
lsSql = "INSERT INTO [ct_bank_checking]([GID],[LINKGID],[LINKTYPE],[VOUDATE],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME])"
|
|
+ " VALUES('" + newGID + "','" + sGID + "','银行日记账','" + sVOUDATE + "','" + strCtSTARTGID + "','" + companyid + "','" + userid + "',getdate())";
|
|
cmdSQL = db.GetSqlStringCommand(lsSql);
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("update ct_bank_statement set CHECKINGGID='" + newGID + "' where STARTGID='" + strCtSTARTGID + "' and GID='" + ds.Tables[0].Rows[i]["GID"].ToString() + "'");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("update ct_bank_journal set CHECKINGGID='" + newGID + "' where STARTGID='" + strCtSTARTGID + "' and GID='" + sGID + "'");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
tran.Commit();
|
|
iDZS++;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
}
|
|
}
|
|
#endregion
|
|
}
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
#endregion
|
|
|
|
if (iDZS > 0)
|
|
{
|
|
result.Success = true;
|
|
result.Message = "成功对账 " + iDZS + "对对账单记录！";
|
|
}
|
|
else
|
|
{
|
|
result.Success = false;
|
|
result.Message = "没有符合条件的对账单记录！";
|
|
}
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
#region 手工对账
|
|
public static DBResult onAddClick(string gidJ, string gidS, string sACCGID, string sVOUDATE, string companyid, string userid)
|
|
{
|
|
#region 默认值
|
|
var result = new DBResult();
|
|
string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid);
|
|
string sGidS = "'" + gidS.Replace(",", "','") + "'";
|
|
string sGidJ = "'" + gidJ.Replace(",", "','") + "'";
|
|
string newGID = "hand" + Guid.NewGuid().ToString().Replace("-", "");
|
|
#endregion
|
|
|
|
#region 更新对账单
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
try
|
|
{
|
|
string lsSql = "INSERT INTO [ct_bank_checking]([GID],[LINKGID],[LINKTYPE],[VOUDATE],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME])"
|
|
+ " select '" + newGID + "',GID,'银行对账单','" + sVOUDATE + "','" + strCtSTARTGID + "','" + companyid + "','" + userid + "',getdate() from ct_bank_statement where STARTGID='" + strCtSTARTGID + "' and GID in (" + sGidS + ")";
|
|
var cmdSQL = db.GetSqlStringCommand(lsSql);
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
lsSql = "INSERT INTO [ct_bank_checking]([GID],[LINKGID],[LINKTYPE],[VOUDATE],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME])"
|
|
+ " select '" + newGID + "',GID,'银行日记账','" + sVOUDATE + "','" + strCtSTARTGID + "','" + companyid + "','" + userid + "',getdate() from ct_bank_journal where STARTGID='" + strCtSTARTGID + "' and GID in (" + sGidJ + ")";
|
|
cmdSQL = db.GetSqlStringCommand(lsSql);
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("update ct_bank_statement set CHECKINGGID='" + newGID + "' where STARTGID='" + strCtSTARTGID + "' and GID in (" + sGidS + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("update ct_bank_journal set CHECKINGGID='" + newGID + "' where GID in (" + sGidJ + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
result.Success = true;
|
|
result.Message = "出错，请重新操作！";
|
|
return result;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
result.Success = true;
|
|
result.Message = "操作成功！";
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
#region 取消对账
|
|
public static DBResult Delete(string gidJ, string gidS, string sql, string sCbVOUDATE, string sACCGID, string companyid, string userid)
|
|
{
|
|
#region 默认值
|
|
var result = new DBResult();
|
|
string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid);
|
|
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
|
|
var sGidS = "'" + gidS.Replace(",", "','") + "'";
|
|
var sGidJ = "'" + gidJ.Replace(",", "','") + "'";
|
|
#endregion
|
|
|
|
if (sCbVOUDATE == "0")
|
|
{
|
|
#region 所选数据
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
try
|
|
{
|
|
#region 所选数据（暂时不用，不对应删除）
|
|
//var cmdSQL = db.GetSqlStringCommand("update ct_bank_statement set CHECKINGGID='' where STARTGID='" + strCtSTARTGID + "' and GID in (" + sGidS + ")");
|
|
//db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
//cmdSQL = db.GetSqlStringCommand("update ct_bank_journal set CHECKINGGID='' where STARTGID='" + strCtSTARTGID + "' and GID in (" + sGidJ + ")");
|
|
//db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
//cmdSQL = db.GetSqlStringCommand("delete from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and LINKGID in (" + sGidS + ") and LINKTYPE='银行对账单'");
|
|
//db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
//cmdSQL = db.GetSqlStringCommand("delete from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and LINKGID in (" + sGidJ + ") and LINKTYPE='银行日记账'");
|
|
//db.ExecuteNonQuery(cmdSQL, tran);
|
|
#endregion
|
|
|
|
var cmdSQL = db.GetSqlStringCommand("update ct_bank_statement set CHECKINGGID='' where STARTGID='" + strCtSTARTGID + "' and CHECKINGGID in (" + sGidS + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("update ct_bank_journal set CHECKINGGID='' where STARTGID='" + strCtSTARTGID + "' and CHECKINGGID in (" + sGidJ + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("delete from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and GID in (" + sGidS + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("delete from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and GID in (" + sGidJ + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
result.Success = true;
|
|
result.Message = "出错，请重新操作！";
|
|
return result;
|
|
}
|
|
}
|
|
#endregion
|
|
}
|
|
else
|
|
{
|
|
#region 日期范围
|
|
if (!string.IsNullOrEmpty(sql))
|
|
{
|
|
sql = "select gid from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and " + sql;
|
|
}
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
try
|
|
{
|
|
var cmdSQL = db.GetSqlStringCommand("update ct_bank_statement set CHECKINGGID='' where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + sACCGID + "' and CHECKINGGID in (" + sql + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("update ct_bank_journal set CHECKINGGID='' where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + sACCGID + "' and CHECKINGGID in (" + sql + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
cmdSQL = db.GetSqlStringCommand("delete from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and GID in (" + sql + ")");
|
|
db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
result.Success = true;
|
|
result.Message = "出错，请重新操作！";
|
|
return result;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 日期范围（暂时不用，不对应删除）
|
|
//string sSQL = "select GID from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and GID in (select CHECKINGGID from ct_bank_statement where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + sACCGID + "' and GID in (select LINKGID from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and LINKTYPE='银行对账单' " + sql + ")) or GID in (select CHECKINGGID from ct_bank_journal where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + sACCGID + "' and GID in (select LINKGID from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and LINKTYPE='银行日记账' " + sql + ")) group by GID";
|
|
//DataSet ds = T_ALL_DA.GetAllSQL(sSQL);
|
|
//if (ds != null)
|
|
//{
|
|
// if (ds.Tables[0].Rows.Count > 0)
|
|
// {
|
|
// Database db = DatabaseFactory.CreateDatabase();
|
|
// using (var conn = db.CreateConnection())
|
|
// {
|
|
// conn.Open();
|
|
// var tran = conn.BeginTransaction();
|
|
// try
|
|
// {
|
|
// var cmdSQL = db.GetSqlStringCommand("update ct_bank_statement set CHECKINGGID='' where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + sACCGID + "' and GID in (select LINKGID from ct_bank_checking where STARTGID='" + strCtSTARTGID + "'" + sql + ")");
|
|
// db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
// cmdSQL = db.GetSqlStringCommand("update ct_bank_journal set CHECKINGGID='' where STARTGID='" + strCtSTARTGID + "' and ACCGID='" + sACCGID + "' and GID in (select LINKGID from ct_bank_checking where STARTGID='" + strCtSTARTGID + "'" + sql + ")");
|
|
// db.ExecuteNonQuery(cmdSQL, tran);
|
|
|
|
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
|
// {
|
|
// cmdSQL = db.GetSqlStringCommand("delete from ct_bank_checking where STARTGID='" + strCtSTARTGID + "' and GID='" + ds.Tables[0].Rows[i]["GID"].ToString() + "'");
|
|
// db.ExecuteNonQuery(cmdSQL, tran);
|
|
// }
|
|
|
|
// tran.Commit();
|
|
// }
|
|
// catch (Exception)
|
|
// {
|
|
// tran.Rollback();
|
|
// result.Success = true;
|
|
// result.Message = "出错，请重新操作！";
|
|
// return result;
|
|
// }
|
|
// }
|
|
// }
|
|
//}
|
|
#endregion
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "操作成功！";
|
|
return result;
|
|
}
|
|
#endregion
|
|
}
|
|
}
|