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.
242 lines
19 KiB
C#
242 lines
19 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.RptCtBankJSCheckingDAL
|
|
{
|
|
public class RptCtBankJSCheckingDAL
|
|
{
|
|
#region 查询主表
|
|
static public List<MsCtBankJournal> GetDataList(string strCondition, string userid, string usercode, string companyid,string sort = null)
|
|
{
|
|
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 gid=ct_bank_journal.ACCGID),SETTLETYPENAME=isnull((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),QTYBLC=CAST((BLC*FCYEXRATE) as decimal) from ct_bank_journal WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "' and CHECKINGGID=''");
|
|
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);
|
|
}
|
|
|
|
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.ISINITIAL = (reader["ISINITIAL"] == null ? false : Convert.ToBoolean(reader["ISINITIAL"]));//是否期初
|
|
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.QTYBLC = (reader["QTYBLC"] == null ? 0 : Convert.ToDecimal(reader["QTYBLC"]));//本位币余额
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
static public List<MsCtBankStatement> GetDataList2(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 CHECKINGGID=''");// 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 SetData2(strSql);
|
|
}
|
|
|
|
private static List<MsCtBankStatement> SetData2(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.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 string GetDataListStr(string strCondition, string sTYPE, string userid, string usercode, string companyid)
|
|
{
|
|
string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid);
|
|
var strSql = new StringBuilder();
|
|
if (strCondition.IndexOf("RMB") > -1 )
|
|
{
|
|
strSql.Append("select GID as 唯一编码,VOUDATE as 凭证日期,ITEMNO as 当日序号,ACCDATE as 会计期间,EXPLAN as 摘要,VKNO as 凭证字,VOUNO as 凭证号,FENTRYID as 分录号,SETTLETYPENAME as 结算方式,BILLNO as 结算号,DR as 借方,CR as 贷方,DC as 方向,BLC as 余额,ENTERED as 经手人,CREATEUSERNAME as 制单人 from (");
|
|
}
|
|
else
|
|
{
|
|
strSql.Append("select GID as 唯一编码,VOUDATE as 凭证日期,ITEMNO as 当日序号,ACCDATE as 会计期间,EXPLAN as 摘要,VKNO as 凭证字,VOUNO as 凭证号,FENTRYID as 分录号,SETTLETYPENAME as 结算方式,BILLNO as 结算号,DR as 借方原币,QTYDR as 借方本位币,CR as 贷方原币,QTYCR as 贷方本位币,DC as 方向,BLC as 余额原币,QTYBLC as 余额本位币,ENTERED as 经手人,CREATEUSERNAME as 制单人 from (");
|
|
}
|
|
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 gid=ct_bank_journal.ACCGID),SETTLETYPENAME=isnull((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),QTYBLC=CAST((BLC*FCYEXRATE) as decimal) from ct_bank_journal WITH(NOLOCK) where STARTGID='" + strCtSTARTGID + "'");
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" and " + strCondition);
|
|
}
|
|
strSql.Append(") as ls");
|
|
if (sTYPE != "export")
|
|
{
|
|
strSql.Append(" order by VOUDATE,convert(decimal(10),ITEMNO)");
|
|
}
|
|
return strSql.ToString();
|
|
}
|
|
|
|
static public string GetDataListStr2(string strCondition, string sTYPE, string userid, string usercode, string companyid)
|
|
{
|
|
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(userid);
|
|
string strCtSTARTGID = BasicDataRefDAL.GetCtSTARTGID(userid);
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("select GID as 唯一编码,VOUDATE as 凭证日期,ITEMNO as 当日序号,ACCDATE as 会计期间,EXPLAN as 摘要,SETTLETYPENAME as 结算方式,BILLNO as 结算号,DR as 银行借方,CR as 银行贷方,DC as 方向,BLC as 余额,ENTERED as 经手人,CREATEUSERNAME as 制单人 from (");
|
|
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);
|
|
}
|
|
strSql.Append(") as ls");
|
|
if (sTYPE != "export")
|
|
{
|
|
strSql.Append(" order by VOUDATE,convert(decimal(10),ITEMNO)");
|
|
}
|
|
return strSql.ToString();
|
|
}
|
|
#endregion
|
|
}
|
|
}
|