dengyu
/
DS7
1
0
Fork 0
Code Issues Pull Requests Projects 1 Releases Wiki Activity
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.
dev
DS7_HYRD
YingHang
ShenZhenCHJY
DS7_JinGang
QDHHYT
master
临时发布20240103
DS7_JingHan
DS7Zhanhao
HuanHaiYunTong
shenzhenchjy
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
DS7/DSWeb/Areas/MvcShipping/DAL/MsCwAccitemsGl/MsCwAccitemsGlDAL.cs

1131 lines
70 KiB
C#
Raw Permalink Normal View History Unescape Escape

'first'
2 years ago
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.MvcShipping.Models.MsCwAccitemsGl;
using DSWeb.MvcShipping.Models.MsCwGenlegAccitems;
using DSWeb.MvcShipping.Models.MsCwAccitemsCurrency;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.EntityDA;
using DSWeb.Areas.CommMng.Models;
using HcUtility.Comm;
using System.Data.SqlClient;
using DSWeb.DataAccess;
using System.Web;
using System.Web.Script.Serialization;
using DSWeb.Areas.CommMng.DAL;
namespace DSWeb.MvcShipping.DAL.MsCwAccitemsGlDAL
{
public class MsCwAccitemsGlDAL
{
#region 查询
static public List<MsCwAccitemsGl> GetDataList(string strCondition, string strUserID)
{
string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(strUserID);
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUserID);
var strSql = new StringBuilder();
strSql.Append("SELECT *,PACCID=(select top 1 ACCID from [cw_accitems_gl] as a where a.gid=cw_accitems_gl.PACCGID),PACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] as b where b.gid=cw_accitems_gl.PACCGID),gid as [id],ACCID+' '+ACCNAME as [NAME],0 as DR,0 as CR");
strSql.Append(" from [cw_accitems_gl] where [YEAR]=SUBSTRING('" + strCwACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "'");
//
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(strCondition);
}
strSql.Append(" order by [YEAR],ACCID");
return SetData(strSql);
}
static public MsCwAccitemsGl GetData(string condition, string strUserID)
{
string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(strUserID);
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUserID);
var strSql = new StringBuilder();
strSql.Append("SELECT *,PACCID=(select top 1 ACCID from [cw_accitems_gl] as a where a.gid=cw_accitems_gl.PACCGID),PACCNAME=(select top 1 ACCNAME from [cw_accitems_gl] as b where b.gid=cw_accitems_gl.PACCGID),gid as [id],ACCID+' '+ACCNAME as [NAME],0 as DR,0 as CR from cw_accitems_gl where [YEAR]=SUBSTRING('" + strCwACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "'");
if (!string.IsNullOrEmpty(condition))
{
condition = condition.Replace("root","0");
strSql.Append(" and " + condition);
}
var list=SetData(strSql);
if (list.Count > 0)
return list[0];
return new MsCwAccitemsGl();
}
private static List<MsCwAccitemsGl> SetData(StringBuilder strSql)
{
var headList = new List<MsCwAccitemsGl>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsCwAccitemsGl data = new MsCwAccitemsGl();
#region Set DB data to Object
data.GID = (reader["GID"] == null ? "" : Convert.ToString(reader["GID"]).Trim());//唯一编码
data.ACCID = (reader["ACCID"] == null ? "" : Convert.ToString(reader["ACCID"]).Trim());//科目代码
data.ACCNAME = (reader["ACCNAME"] == null ? "" : Convert.ToString(reader["ACCNAME"]).Trim());//科目名称
data.DETAILED = (reader["DETAILED"] == null ? false : Convert.ToBoolean(reader["DETAILED"]));//是否末级科目(标志)
data.DC = (reader["DC"] == null ? "" : Convert.ToString(reader["DC"]).Trim());//借贷方向
data.ISFCY = (reader["ISFCY"] == null ? false : Convert.ToBoolean(reader["ISFCY"]));//是否核算外币
data.ISDEPTACC = (reader["ISDEPTACC"] == null ? false : Convert.ToBoolean(reader["ISDEPTACC"]));//是否核算部门
data.ISEMPLACC = (reader["ISEMPLACC"] == null ? false : Convert.ToBoolean(reader["ISEMPLACC"]));//是否核算人员
data.ISCORPACC = (reader["ISCORPACC"] == null ? false : Convert.ToBoolean(reader["ISCORPACC"]));//是否算客户门
data.ISITEMACC = (reader["ISITEMACC"] == null ? false : Convert.ToBoolean(reader["ISITEMACC"]));//是否核算项目
data.REMARKS = (reader["REMARKS"] == null ? "" : Convert.ToString(reader["REMARKS"]).Trim());//备注
data.YEAR = (reader["YEAR"] == null ? "" : Convert.ToString(reader["YEAR"]).Trim());//年
data.MONTH = (reader["MONTH"] == null ? "" : Convert.ToString(reader["MONTH"]).Trim());//月
data.PACCGID = (reader["PACCGID"] == null ? "" : Convert.ToString(reader["PACCGID"]).Trim());//父级科目代码
data.PACCID = (reader["PACCID"] == null ? "" : Convert.ToString(reader["PACCID"]).Trim());//父级科目
data.ACCATTRIBUTE = (reader["ACCATTRIBUTE"] == null ? "" : Convert.ToString(reader["ACCATTRIBUTE"]).Trim());//科目属性（资产、负债、共同、权益、成本、损益）
data.ISENABLE = (reader["ISENABLE"] == null ? false : Convert.ToBoolean(reader["ISENABLE"]));//是否启用
data.ACCTYPE = (reader["ACCTYPE"] == null ? "" : Convert.ToString(reader["ACCTYPE"]).Trim());
data.STARTGID = (reader["STARTGID"] == null ? "" : Convert.ToString(reader["STARTGID"]));
data.id = (reader["GID"] == null ? "" : Convert.ToString(reader["GID"]).Trim());//唯一编码
data.NAME = (reader["NAME"] == null ? "" : Convert.ToString(reader["NAME"]).Trim());//
if (data.DETAILED.ToString().Trim().ToLower() == "true")
{
data.leaf = true;
data.expanded = false;
}
else
{
data.leaf = false;
data.expanded = true;
}
data.DR = (reader["DR"] == null ? 0 : Convert.ToDecimal(reader["DR"]));
data.CR = (reader["CR"] == null ? 0 : Convert.ToDecimal(reader["CR"]));
data.ISBANK = (reader["ISBANK"] == null ? false : Convert.ToBoolean(reader["ISBANK"]));//是否银行类科目
data.ISSETTLEMENT = (reader["ISSETTLEMENT"] == null ? false : Convert.ToBoolean(reader["ISSETTLEMENT"]));//是否结算类科目
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region 删除
public static DBResult DeleteDetail(MsCwAccitemsGl headData)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
int inum = int.Parse(T_ALL_DA.GetStrSQL("inum", "select count(*) as inum from cw_accitems_gl where GID<>'" + headData.GID + "' and PACCGID='" + headData.PACCGID + "'"));
if (inum == 0)
{
var cmdDelete2 = db.GetSqlStringCommand("update cw_accitems_gl set DETAILED=1 where gid='" + headData.PACCGID.ToString() + "'");
db.ExecuteNonQuery(cmdDelete2, tran);
}
var cmdDelete = db.GetSqlStringCommand("delete from cw_accitems_currency where LINKGID='" + headData.GID + "'");
db.ExecuteNonQuery(cmdDelete, tran);
cmdDelete = db.GetSqlStringCommand("delete from cw_genleg_accitems where LINKGID='" + headData.GID + "'");
db.ExecuteNonQuery(cmdDelete, tran);
cmdDelete = db.GetSqlStringCommand("delete from cw_accitems_gl where GID='" + headData.GID + "'");
db.ExecuteNonQuery(cmdDelete, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "删除出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "操作成功";
return result;
}
#endregion
#region 复制上年科目（暂时废弃，迁移到账套启用模块的“启用”功能中）
public static DBResult onCopy(string strCOMPANYID, string strUSERID)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(strUSERID);
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUSERID);
string sqlCwSTARTGID = "";//无 当前账套往年的数据。
#region cw_accitems_gl 科目信息表
string sSQL = "select top 1 [YEAR]+'-'+[month] as ACCDATE from cw_accitems_gl where [STARTGID]='" + strCwSTARTGID + "' and [YEAR]=(SUBSTRING('" + strCwACCDATE + "',1,4)) group by [YEAR],[month] order by [YEAR] desc,[month] desc";
string strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
#region cw_accitems_gl 判断是否有当前账套往年的数据
sSQL = "select top 1 [YEAR]+'-'+[month] as ACCDATE from cw_accitems_gl where [STARTGID]='" + strCwSTARTGID + "' group by [YEAR],[month] order by [YEAR] desc,[month] desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
sSQL = "select top 1 [YEAR]+'-'+[month] as ACCDATE from cw_accitems_gl group by [YEAR],[month] order by [YEAR] desc,[month] desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
}
else
{
sqlCwSTARTGID = " and [STARTGID]='" + strCwSTARTGID + "'";//有 当前账套往年的数据。
}
#endregion
#region cw_accitems_gl 复制插入操作新科目
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
#region cw_accitems_gl 根据现有数据，复制插入新科目
sSQL = "insert into cw_accitems_gl (GID,ACCID,ACCNAME,DETAILED,DC,ISFCY,ISDEPTACC,ISEMPLACC,ISCORPACC,ISITEMACC,REMARKS,YEAR,MONTH,PACCGID,ACCATTRIBUTE,ISENABLE,ACCTYPE,STARTGID)"
+ "(select SUBSTRING(GID,1,LEN(GID)-7)+'" + strCwACCDATE + "' as GID,ACCID,ACCNAME,DETAILED,DC,ISFCY,ISDEPTACC,ISEMPLACC,ISCORPACC,ISITEMACC,REMARKS,SUBSTRING('" + strCwACCDATE + "',1,4) as [YEAR],SUBSTRING('" + strCwACCDATE + "',6,2) as [MONTH]"
+ ",(case when (PACCGID='ZC' or PACCGID='FZ' or PACCGID='GT' or PACCGID='QY' or PACCGID='CB' or PACCGID='SY') then PACCGID else SUBSTRING(PACCGID,1,LEN(PACCGID)-7)+'" + strCwACCDATE + "' end)"
+ ",ACCATTRIBUTE,0 as ISENABLE,ACCTYPE,'" + strCwSTARTGID + "'"
+ " from cw_accitems_gl where [YEAR]=(SUBSTRING('" + strDescACCDATE + "',1,4))" + sqlCwSTARTGID + ")";// and gid not in (select gid from cw_accitems_gl where [YEAR]=SUBSTRING('" + strCwACCDATE + "',1,4)" + sqlCwSTARTGID + ")
#endregion
var cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
#region cw_accitems_gl 根据现有数据，更新新科目的父类GID
string sSqlYuan = "SELECT GID,ACCID,ACCNAME,DETAILED,DC,ISFCY,ISDEPTACC,ISEMPLACC,ISCORPACC,ISITEMACC,REMARKS,[YEAR],[MONTH],PACCGID=(case when (PACCGID='ZC' or PACCGID='FZ' or PACCGID='GT' or PACCGID='QY' or PACCGID='CB' or PACCGID='SY') then '0' else PACCGID end),ACCATTRIBUTE,ISENABLE,ACCTYPE from [cw_accitems_gl] WITH(NOLOCK) where [YEAR]=SUBSTRING('" + strDescACCDATE + "',1,4)" + sqlCwSTARTGID + " order by [YEAR],ACCID";// and PACCGID not in('ZC','FZ','GT','QY','CB','SY')
DataSet dsYuan = T_ALL_DA.GetAllSQL(sSqlYuan);
if (dsYuan != null)
{
if (dsYuan.Tables[0].Rows.Count > 0)
{
string sSqlXian = "SELECT GID,ACCID,ACCNAME,DETAILED,DC,ISFCY,ISDEPTACC,ISEMPLACC,ISCORPACC,ISITEMACC,REMARKS,[YEAR],[MONTH],PACCGID=(case when (PACCGID='ZC' or PACCGID='FZ' or PACCGID='GT' or PACCGID='QY' or PACCGID='CB' or PACCGID='SY') then '0' else PACCGID end),ACCATTRIBUTE,ISENABLE,ACCTYPE from [cw_accitems_gl] WITH(NOLOCK) where [YEAR]=SUBSTRING('" + strCwACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "' order by [YEAR],ACCID";// and PACCGID not in('ZC','FZ','GT','QY','CB','SY')
DataSet dsXian = T_ALL_DA.GetAllSQL(sSqlXian);
if (dsXian != null)
{
if (dsXian.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsYuan.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < dsXian.Tables[0].Rows.Count; j++)
{
if (dsYuan.Tables[0].Rows[i]["GID"].ToString().Trim() == dsXian.Tables[0].Rows[j]["PACCGID"].ToString().Trim() && dsXian.Tables[0].Rows[j]["PACCGID"].ToString().Trim() != "0")
{
for (int n = 0; n < dsXian.Tables[0].Rows.Count; n++)
{
if (dsYuan.Tables[0].Rows[i]["ACCID"].ToString().Trim() == dsXian.Tables[0].Rows[n]["ACCID"].ToString().Trim())
{
string sSQL4 = "update cw_accitems_gl set PACCGID='" + dsXian.Tables[0].Rows[n]["GID"].ToString().Trim() + "' where PACCGID='" + dsYuan.Tables[0].Rows[i]["GID"].ToString().Trim() + "' and [YEAR]=SUBSTRING('" + strCwACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "'";
var cmd4 = db.GetSqlStringCommand(sSQL4);
db.ExecuteNonQuery(cmd4, tran);
}
}
}
}
}
}
}
}
}
#endregion
//cw_accitems_currency 科目币别信息
sSQL = "INSERT INTO [cw_accitems_currency]([GID],[LINKGID],[CURRENCY],[CORPID],[CREATEUSER],[CREATETIME])"
+ " SELECT NEWID() as [GID]"
+ ",[LINKGID]=(SELECT top 1 GID from cw_accitems_gl where STARTGID='" + strCwSTARTGID + "' and accid=(select top 1 accid from cw_accitems_gl where gid=cw_accitems_currency.LINKGID))"
+ ",[CURRENCY],'" + strCOMPANYID + "' as [CORPID],'" + strUSERID + "' as [CREATEUSER],GETDATE() as [CREATETIME]"
+ " FROM [cw_accitems_currency] WITH(NOLOCK) where LINKGID in (select GID from cw_accitems_gl where [YEAR]=SUBSTRING('" + strDescACCDATE + "',1,4) and STARTGID<>'" + strCwSTARTGID + "')";//" + sqlCwSTARTGID + "
cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "操作错误，请重试！";
return result;
}
}
#endregion
}
#endregion
#region cw_currency_rate 根据现有数据，复制插入“总账汇率表”的数据
DataSet ds = T_ALL_DA.GetAllSQL("select * from code_currency where CODENAME<>'RMB'");
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MsCwCurrencyRateDAL.MsCwCurrencyRateDAL.isSaveRow(ds.Tables[0].Rows[i]["CODENAME"].ToString(), strUSERID, strCOMPANYID);
}
}
}
#endregion
#region cw_sofp_template 资产负债表
string TEMPLATE = "formRptCwSofpIndex";
sSQL = "select top 1 ACCDATE from cw_sofp_template WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and ACCDATE='" + strCwACCDATE + "' and TEMPLATE='" + TEMPLATE + "' group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
#region cw_sofp_template 判断是否有当前账套往年的数据
sSQL = "select top 1 ACCDATE from cw_sofp_template WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and TEMPLATE='" + TEMPLATE + "' group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
sqlCwSTARTGID = "";//无 当前账套往年的数据。
sSQL = "select top 1 ACCDATE from cw_sofp_template WITH(NOLOCK) where TEMPLATE='" + TEMPLATE + "' group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
}
else
{
sqlCwSTARTGID = " and [STARTGID]='" + strCwSTARTGID + "'";//有 当前账套往年的数据。
}
#endregion
#region cw_sofp_template 根据现有数据，复制插入“资产负债表”的数据
if (strDescACCDATE != strCwACCDATE)
{
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
//主表
sSQL = "INSERT INTO [cw_sofp_template]([GID],[TEMPLATE],[ACCDATE],[LINENUM],[LINENUM1],[ACCTYPE],[QTYLASTBLC],[QTYYEARBLC],[LINENUM2],[ACCTYPE2],[QTYLASTBLC2],[QTYYEARBLC2],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME]) "
+ "SELECT NEWID() as [GID],[TEMPLATE],'" + strCwACCDATE + "' as [ACCDATE],[LINENUM],[LINENUM1],[ACCTYPE],0 as [QTYLASTBLC],0 as [QTYYEARBLC],[LINENUM2],[ACCTYPE2],0 as [QTYLASTBLC2],0 as [QTYYEARBLC2],'" + strCwSTARTGID + "' as [STARTGID],'" + strCOMPANYID + "' as [CORPID],'" + strUSERID + "' as [MODIFIEDUSER],GETDATE() FROM [cw_sofp_template] where ACCDATE='" + strDescACCDATE + "' and TEMPLATE='" + TEMPLATE + "'" + sqlCwSTARTGID;
var cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
//明细表
sSQL = "INSERT INTO [cw_sofp_template_detail]([GID],[LINKGID],[LINKNUM],[ACCGID],[OPERATORS],[ACCESS],[QTYLASTBLC],[QTYYEARBLC],[MODIFIEDUSER],[MODIFIEDTIME]) "
+ "SELECT NEWID() as [GID]"
+ ",[LINKGID]=ISNULL((SELECT top 1 [GID] FROM [cw_sofp_template] where LINENUM=(select top 1 LINENUM from cw_sofp_template WITH(NOLOCK) where gid=cw_sofp_template_detail.LINKGID) and ACCDATE='" + strCwACCDATE + "' and [STARTGID]='" + strCwSTARTGID + "'),'')"
+ ",[LINKNUM]"
+ ",[ACCGID]=ISNULL((SELECT top 1 [GID] FROM [cw_accitems_gl] where [STARTGID]='" + strCwSTARTGID + "' and accid=(select top 1 accid from cw_accitems_gl where gid=cw_sofp_template_detail.ACCGID)),'')"
+ ",[OPERATORS],[ACCESS],0 as [QTYLASTBLC],0 as [QTYBLC],'" + strUSERID + "' as [MODIFIEDUSER],GETDATE() FROM [cw_sofp_template_detail] WITH(NOLOCK) where LINKGID in (SELECT [GID] FROM [cw_sofp_template] WITH(NOLOCK) where ACCDATE='" + strDescACCDATE + "' and TEMPLATE='" + TEMPLATE + "'" + sqlCwSTARTGID + ")";
cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "操作错误，请重试！";
return result;
}
}
}
#endregion
}
#endregion
#region cw_pl_template 利润表
TEMPLATE = "formRptCwPLIndex";
sSQL = "select top 1 ACCDATE from cw_pl_template WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and ACCDATE='" + strCwACCDATE + "' and TEMPLATE='" + TEMPLATE + "' group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
#region cw_pl_template 判断是否有当前账套往年的数据
sSQL = "select top 1 ACCDATE from cw_pl_template WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and TEMPLATE='" + TEMPLATE + "' group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
sqlCwSTARTGID = "";//无 当前账套往年的数据。
sSQL = "select top 1 ACCDATE from cw_pl_template WITH(NOLOCK) where TEMPLATE='" + TEMPLATE + "' group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
}
else
{
sqlCwSTARTGID = " and [STARTGID]='" + strCwSTARTGID + "'";//有 当前账套往年的数据。
}
#endregion
#region cw_pl_template 根据现有数据，复制插入“利润表”的数据
if (strDescACCDATE != strCwACCDATE)
{
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
//主表
sSQL = "INSERT INTO [cw_pl_template]([GID],[TEMPLATE],[ACCDATE],[LINENUM],[ACCTYPE],[QTYLASTBLC],[QTYBLC],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME]) "
+ "SELECT NEWID() as [GID],[TEMPLATE],'" + strCwACCDATE + "' as [ACCDATE],[LINENUM],[ACCTYPE],0 as [QTYLASTBLC],0 as [QTYBLC],'" + strCwSTARTGID + "' as [STARTGID],'" + strCOMPANYID + "' as [CORPID],'" + strUSERID + "' as [MODIFIEDUSER],GETDATE() FROM [cw_pl_template] WITH(NOLOCK) where ACCDATE='" + strDescACCDATE + "' and TEMPLATE='" + TEMPLATE + "'" + sqlCwSTARTGID;
var cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
//明细表
sSQL = "INSERT INTO [cw_pl_template_detail]([GID],[LINKGID],[ACCGID],[OPERATORS],[QTYLASTBLC],[QTYBLC],[MODIFIEDUSER],[MODIFIEDTIME]) "
+ "SELECT NEWID() as [GID]"
+ ",[LINKGID]=(SELECT top 1 [GID] FROM [cw_pl_template] where LINENUM=(select top 1 LINENUM from cw_pl_template WITH(NOLOCK) where gid=cw_pl_template_detail.LINKGID) and ACCDATE='" + strCwACCDATE + "' and [STARTGID]='" + strCwSTARTGID + "')"
+ ",[ACCGID]=(SELECT top 1 [GID] FROM [cw_accitems_gl] where [STARTGID]='" + strCwSTARTGID + "' and accid=(select top 1 accid from cw_accitems_gl where gid=cw_pl_template_detail.ACCGID))"
+ ",[OPERATORS],0 as [QTYLASTBLC],0 as [QTYBLC],'" + strUSERID + "' as [MODIFIEDUSER],GETDATE() FROM [cw_pl_template_detail] WITH(NOLOCK) where LINKGID in (SELECT [GID] FROM [cw_pl_template] WITH(NOLOCK) where ACCDATE='" + strDescACCDATE + "' and TEMPLATE='" + TEMPLATE + "'" + sqlCwSTARTGID + ")";
cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "操作错误，请重试！";
return result;
}
}
}
#endregion
}
#endregion
#region cw_cfs_model 现金流量表模板
strDescACCDATE = "";
sSQL = "select top 1 GID from [cw_cfs_model] WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and ISDEFAULT=1 order by MODIFIEDTIME";
string newMODELGID = T_ALL_DA.GetStrSQL("GID", sSQL);
if (newMODELGID == "")
{
newMODELGID = Guid.NewGuid().ToString();
#region cw_cfs_model 插入当前账套的模板数据及明细表
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
//主表
sSQL = "INSERT INTO [cw_cfs_model]([GID],[MODELNAME],[ISDEFAULT],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME]) "
+ " VALUES('" + newMODELGID + "','默认方案',1,'" + strCwSTARTGID + "' ,'" + strCOMPANYID + "','" + strUSERID + "',GETDATE())";
var cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
//明细表
sSQL = "INSERT INTO [cw_cfs_model_item]([GID],[MODELGID],[ITEMNAME],[LINENUM],[DETAILED],[ISALTER],[PACCGID],[ITEMTYPE],[MODIFIEDUSER],[MODIFIEDTIME]) "
+ "SELECT NEWID() as [GID],'" + newMODELGID + "' as [MODELGID],[ITEMNAME],[LINENUM],[DETAILED],[ISALTER]"
+ ",[PACCGID]=(select top 1 GID from [cw_cfs_model_item] as b where b.LINENUM=(select top 1 LINENUM from [cw_cfs_model_item] as a where a.gid=[cw_cfs_model_item].[PACCGID]) and [MODELGID]='" + newMODELGID + "')"
+ ",[ITEMTYPE],'" + strUSERID + "' as [MODIFIEDUSER],GETDATE() as [MODIFIEDTIME] FROM [cw_cfs_model_item] WITH(NOLOCK) where MODELGID=(SELECT top 1 [GID] FROM [cw_cfs_model] WITH(NOLOCK) where [STARTGID]<>'" + strCwSTARTGID + "' and ISDEFAULT=1)";
cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "操作错误，请重试！";
return result;
}
}
#endregion
}
#endregion
#region cw_cfs_template 现金流量表
sqlCwSTARTGID = "";//无 当前账套往年的数据。
sSQL = "select top 1 ACCDATE from cw_cfs_template WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and ACCDATE='" + strCwACCDATE + "' group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
#region cw_cfs_template 判断是否有当前账套往年的数据
sSQL = "select top 1 ACCDATE from cw_cfs_template WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
sSQL = "select top 1 ACCDATE from cw_cfs_template WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and MODELGID=(select top 1 GID from cw_cfs_model where [STARTGID]='" + strCwSTARTGID + "' order by MODIFIEDTIME) group by ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
sSQL = "select top 1 ACCDATE from cw_cfs_template WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' group by MODELGID,ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
if (strDescACCDATE == "")
{
sSQL = "select top 1 ACCDATE from cw_cfs_template WITH(NOLOCK) group by MODELGID,ACCDATE order by ACCDATE desc";
strDescACCDATE = T_ALL_DA.GetStrSQL("ACCDATE", sSQL);
}
else
{
sqlCwSTARTGID = " and [STARTGID]='" + strCwSTARTGID + "'";//有 当前账套往年的数据。
}
}
else
{
sqlCwSTARTGID = " and [STARTGID]='" + strCwSTARTGID + "'";//有 当前账套往年的数据。
}
}
#endregion
#region cw_cfs_template 根据现有数据，复制插入“现金流量表”的数据
if (strDescACCDATE != strCwACCDATE)
{
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
//主表
sSQL = "INSERT INTO [cw_cfs_template]([GID],[MODELGID],[ITEMGID],[ACCDATE],[QTYLASTBLC],[QTYBLC],[QTYLASTBLC_S],[QTYBLC_S],[QTYLASTBLC_H],[QTYBLC_H],[STARTGID],[CORPID],[MODIFIEDUSER],[MODIFIEDTIME]) "
+ "SELECT NEWID() as [GID]"
+ ",[MODELGID]=(select top 1 GID from cw_cfs_model where [STARTGID]='" + strCwSTARTGID + "' and ISDEFAULT=1)"
+ ",[ITEMGID]=(select top 1 GID from [cw_cfs_model_item] as b where b.LINENUM=(select top 1 LINENUM from [cw_cfs_model_item] as a where a.gid=[cw_cfs_template].[ITEMGID]) and MODELGID=(select top 1 GID from cw_cfs_model where [STARTGID]='" + strCwSTARTGID + "' and ISDEFAULT=1 order by MODIFIEDTIME))"
+ ",'" + strCwACCDATE + "' as [ACCDATE],0 as [QTYLASTBLC],0 as [QTYBLC],0 as [QTYLASTBLC_S],0 as [QTYBLC_S],0 as [QTYLASTBLC_H],0 as [QTYBLC_H],'" + strCwSTARTGID + "' as [STARTGID],'" + strCOMPANYID + "' as [CORPID],'" + strUSERID + "' as [MODIFIEDUSER],GETDATE() as [MODIFIEDTIME]"
+ " FROM [cw_cfs_template] WITH(NOLOCK) where ACCDATE='" + strDescACCDATE + "'" + sqlCwSTARTGID;
var cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
//明细表
sSQL = "INSERT INTO [cw_cfs_template_detail]([GID],[LINKGID],[ACCGID],[OPERATORS],[ACCESS],[QTYLASTBLC],[QTYBLC],[MODIFIEDUSER],[MODIFIEDTIME]) "
+ "SELECT NEWID() as [GID]"
+ ",[LINKGID]=(SELECT top 1 [GID] FROM [cw_cfs_template] where ITEMGID=(select top 1 GID from [cw_cfs_model_item] where MODELGID=(select top 1 GID from cw_cfs_model where [STARTGID]='" + strCwSTARTGID + "' and ISDEFAULT=1 order by MODIFIEDTIME) and LINENUM=(select top 1 LINENUM from [cw_cfs_model_item] where gid=(select top 1 ITEMGID from cw_cfs_template WITH(NOLOCK) where gid=cw_cfs_template_detail.LINKGID))) and MODELGID=(select top 1 GID from cw_cfs_model where [STARTGID]='" + strCwSTARTGID + "' and ISDEFAULT=1 order by MODIFIEDTIME) and ACCDATE='" + strCwACCDATE + "' and [STARTGID]='" + strCwSTARTGID + "')"
+ ",[ACCGID]=(SELECT top 1 [GID] FROM [cw_accitems_gl] where [STARTGID]='" + strCwSTARTGID + "' and accid=(select top 1 accid from cw_accitems_gl where gid=cw_cfs_template_detail.ACCGID))"
+ ",[OPERATORS],[ACCESS],0 as [QTYLASTBLC],0 as [QTYBLC],'" + strUSERID + "' as [MODIFIEDUSER],GETDATE() as [MODIFIEDTIME]"
+ " FROM [cw_cfs_template_detail] WITH(NOLOCK) where LINKGID in (SELECT [GID] FROM [cw_cfs_template] WITH(NOLOCK) where ACCDATE='" + strDescACCDATE + "'" + sqlCwSTARTGID + ")";
cmd = db.GetSqlStringCommand(sSQL);
db.ExecuteNonQuery(cmd, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "操作错误，请重试！";
return result;
}
}
}
#endregion
}
#endregion
result.Success = true;
result.Message = "操作成功";
return result;
}
//是否有往年数据
public static DBResult isShowCopy(string strACCDATE, string strUSERID)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUSERID);
string strSQL = "select count(*) as inum from cw_accitems_gl where [YEAR]=SUBSTRING('" + strACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "'";
string inum = T_ALL_DA.GetStrSQL("inum", strSQL);
if (inum == "")
{
inum = "0";
strSQL = "select count(*) as inum from cw_accitems_gl where [YEAR]=(SUBSTRING('" + strACCDATE + "',1,4)-1) and [STARTGID]='" + strCwSTARTGID + "'";
inum = T_ALL_DA.GetStrSQL("inum", strSQL);
if (inum == "")
{
inum = "无上年数据!";
}
}
//
result.Success = true;
result.Message = inum;
return result;
}
#endregion
#region 科目编码生成
static public MsCwAccitemsGl GetACCID(string PGID, string PACCID, string OpType, string strUSERID)
{
#region 默认参数
string strACCID = "";
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
MsCwAccitemsGl data = new MsCwAccitemsGl();
Database db = DatabaseFactory.CreateDatabase();
//
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUSERID);
string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(strUSERID);
DateTime rq2 = DateTime.Parse(strCwACCDATE.Trim() + "-01");
string strYEAR = rq2.Year.ToString().Trim();
string strMONTH = rq2.Month.ToString().Trim();
if (strMONTH.Length == 1)
{
strMONTH = "0" + strMONTH;
}
#endregion
if (PGID == "")
{
string strSql = "select top 1 * from cw_accitems_gl where [year]=SUBSTRING('" + strCwACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "' and PACCGID='" + OpType + "' order by ACCID desc";
DataSet ds = T_ALL_DA.GetAllSQL(strSql);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
#region 根据_其他父类科目
data.GID = "*";//唯一编码
string sZH = ds.Tables[0].Rows[0]["ACCID"].ToString().Trim();
int iZH = int.Parse(sZH);
iZH = iZH + 1;
strACCID = iZH.ToString();
for (int i = 0; i < (sZH.Length - strACCID.Length); i++)
{
strACCID = "0" + strACCID;
}
data.ACCID = strACCID;//科目代码
data.ACCNAME = "";//科目名称
data.DETAILED = true;//是否末级科目(标志)
data.DC = ds.Tables[0].Rows[0]["DC"].ToString();//借贷方向
data.ISFCY = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISFCY"].ToString());//是否核算外币
data.ISDEPTACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISDEPTACC"].ToString());//是否核算部门
data.ISEMPLACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISEMPLACC"].ToString());//是否核算人员
data.ISCORPACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISCORPACC"].ToString());//是否算客户门
data.ISITEMACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISITEMACC"].ToString());//是否核算项目
data.REMARKS = ds.Tables[0].Rows[0]["REMARKS"].ToString();//备注
data.YEAR = ds.Tables[0].Rows[0]["YEAR"].ToString();//年
data.MONTH = ds.Tables[0].Rows[0]["MONTH"].ToString();//月
data.PACCGID = OpType;//父级科目代码
data.PACCID = "";//父级科目
data.ACCATTRIBUTE = ds.Tables[0].Rows[0]["ACCATTRIBUTE"].ToString();//科目属性（资产、负债、共同、权益、成本、损益）
data.ACCTYPE = ds.Tables[0].Rows[0]["ACCTYPE"].ToString();
data.ISENABLE = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISENABLE"].ToString());//是否启用
data.id = "*";//唯一编码
data.NAME = "";//
if (data.DETAILED.ToString().Trim().ToLower() == "true")
{
data.leaf = true;
data.expanded = false;
}
else
{
data.leaf = false;
data.expanded = true;
}
data.DR = 0;
data.CR = 0;
#endregion
return data;
}
}
#region 新建第一个父类科目
data.GID = "*";//唯一编码
data.ACCID = "";//科目代码
data.ACCNAME = "";//科目名称
data.DETAILED = true;//是否末级科目(标志)
data.ISFCY = false;//是否核算外币
data.ISDEPTACC = false;//是否核算部门
data.ISEMPLACC = false;//是否核算人员
data.ISCORPACC = false;//是否算客户门
data.ISITEMACC = false;//是否核算项目
data.REMARKS = "";//备注
data.YEAR = strYEAR;//年
data.MONTH = strMONTH;//月
data.PACCGID = OpType;//父级科目代码
data.PACCID = "";//父级科目
if (OpType == "ZC")
{
data.DC = "借";//借贷方向
data.ACCATTRIBUTE = "资产";//科目属性（资产、负债、共同、权益、成本、损益）
data.ACCTYPE = "流动资产";
}
else if (OpType == "FZ")
{
data.DC = "贷";//借贷方向
data.ACCATTRIBUTE = "负债";//科目属性（资产、负债、共同、权益、成本、损益）
data.ACCTYPE = "流动负债";
}
else if (OpType == "GT")
{
data.DC = "借";//借贷方向
data.ACCATTRIBUTE = "共同";//科目属性（资产、负债、共同、权益、成本、损益）
data.ACCTYPE = "共同";
}
else if (OpType == "QY")
{
data.DC = "贷";//借贷方向
data.ACCATTRIBUTE = "权益";//科目属性（资产、负债、共同、权益、成本、损益）
data.ACCTYPE = "所有者权益";
}
else if (OpType == "CB")
{
data.DC = "借";//借贷方向
data.ACCATTRIBUTE = "成本";//科目属性（资产、负债、共同、权益、成本、损益）
data.ACCTYPE = "成本";
}
else if (OpType == "SY")
{
data.DC = "贷";//借贷方向
data.ACCATTRIBUTE = "损益";//科目属性（资产、负债、共同、权益、成本、损益）
data.ACCTYPE = "营业收入";
}
data.ISENABLE = false;//是否启用
data.id = "*";//唯一编码
data.NAME = "";//
if (data.DETAILED.ToString().Trim().ToLower() == "true")
{
data.leaf = true;
data.expanded = false;
}
else
{
data.leaf = false;
data.expanded = true;
}
data.DR = 0;
data.CR = 0;
#endregion
return data;
}
else
{
string strSql = "select top 1 * from cw_accitems_gl where [year]=SUBSTRING('" + strCwACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "' and PACCGID='" + PGID + "' order by ACCID desc";
DataSet ds = T_ALL_DA.GetAllSQL(strSql);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
#region 根据_其他子科目
data.GID = "*";//唯一编码
string sZH = ds.Tables[0].Rows[0]["ACCID"].ToString().Trim().Replace(PACCID, "");
int iZH = int.Parse(sZH);
iZH = iZH + 1;
strACCID = iZH.ToString();
for (int i = 0; i < (sZH.Length - strACCID.Length); i++)
{
strACCID = "0" + strACCID;
}
strACCID = PACCID + strACCID;
data.ACCID = strACCID;//科目代码
data.ACCNAME = "";//科目名称
data.DETAILED = true;//是否末级科目(标志)
data.DC = ds.Tables[0].Rows[0]["DC"].ToString();//借贷方向
data.ISFCY = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISFCY"].ToString());//是否核算外币
data.ISDEPTACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISDEPTACC"].ToString());//是否核算部门
data.ISEMPLACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISEMPLACC"].ToString());//是否核算人员
data.ISCORPACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISCORPACC"].ToString());//是否算客户门
data.ISITEMACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISITEMACC"].ToString());//是否核算项目
data.REMARKS = ds.Tables[0].Rows[0]["REMARKS"].ToString();//备注
data.YEAR = ds.Tables[0].Rows[0]["YEAR"].ToString();//年
data.MONTH = ds.Tables[0].Rows[0]["MONTH"].ToString();//月
data.PACCGID = PGID;//父级科目代码
data.PACCID = PACCID;//父级科目
data.ACCATTRIBUTE = ds.Tables[0].Rows[0]["ACCATTRIBUTE"].ToString();//科目属性（资产、负债、共同、权益、成本、损益）
data.ISENABLE = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISENABLE"].ToString());//是否启用
data.ACCTYPE = ds.Tables[0].Rows[0]["ACCTYPE"].ToString();
data.id = "*";//唯一编码
data.NAME = "";//
if (data.DETAILED.ToString().Trim().ToLower() == "true")
{
data.leaf = true;
data.expanded = false;
}
else
{
data.leaf = false;
data.expanded = true;
}
data.DR = 0;
data.CR = 0;
#endregion
return data;
}
}
//
strSql = "select top 1 * from cw_accitems_gl where [year]=SUBSTRING('" + strCwACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "' and GID='" + PGID + "' order by ACCID desc";
ds = T_ALL_DA.GetAllSQL(strSql);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
#region 根据_父类科目
data.GID = "*";//唯一编码
data.ACCID = PACCID + "01";//科目代码
data.ACCNAME = "";//科目名称
data.DETAILED = true;//是否末级科目(标志)
data.DC = ds.Tables[0].Rows[0]["DC"].ToString();//借贷方向
data.ISFCY = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISFCY"].ToString());//是否核算外币
data.ISDEPTACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISDEPTACC"].ToString());//是否核算部门
data.ISEMPLACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISEMPLACC"].ToString());//是否核算人员
data.ISCORPACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISCORPACC"].ToString());//是否算客户门
data.ISITEMACC = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISITEMACC"].ToString());//是否核算项目
data.REMARKS = ds.Tables[0].Rows[0]["REMARKS"].ToString();//备注
data.YEAR = ds.Tables[0].Rows[0]["YEAR"].ToString();//年
data.MONTH = ds.Tables[0].Rows[0]["MONTH"].ToString();//月
data.PACCGID = PGID;//父级科目代码
data.PACCID = PACCID;//父级科目
data.ACCATTRIBUTE = ds.Tables[0].Rows[0]["ACCATTRIBUTE"].ToString();//科目属性（资产、负债、共同、权益、成本、损益）
data.ACCTYPE = ds.Tables[0].Rows[0]["ACCTYPE"].ToString();
data.ISENABLE = Convert.ToBoolean(ds.Tables[0].Rows[0]["ISENABLE"].ToString());//是否启用
data.id = "*";//唯一编码
data.NAME = "";//
if (data.DETAILED.ToString().Trim().ToLower() == "true")
{
data.leaf = true;
data.expanded = false;
}
else
{
data.leaf = false;
data.expanded = true;
}
data.DR = 0;
data.CR = 0;
#endregion
return data;
}
}
}
return data;
}
#endregion
#region 末级科目新增第一个下级科目
public static DBResult SaveDetailed(MsCwAccitemsGl headData, string PGID, string PACCID, string PACCNAME, string USERID)
{
#region 默认值
var result = new DBResult();
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(USERID);
string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(USERID);
//headData.YEAR = DateTime.Now.Year.ToString();
//headData.MONTH = DateTime.Now.Month.ToString("##");
headData.YEAR = DateTime.Parse(strCwACCDATE + "-01").Year.ToString();
headData.MONTH = DateTime.Parse(strCwACCDATE + "-01").Month.ToString();
if (headData.MONTH.ToString().Length == 1)
{
headData.MONTH = "0" + headData.MONTH.ToString();
}
headData.PACCGID = PGID.Trim();
string sPPGID = T_ALL_DA.GetStrSQL("PACCGID", "select top 1 PACCGID from cw_accitems_gl where GID='" + PGID.Trim() + "'");
string cwPACCNAME = BasicDataRefDAL.setCAGA(sPPGID, PACCNAME);
#endregion
#region 事务1
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
//插入新科目
string sSQL = "INSERT INTO [cw_accitems_gl]([GID],[ACCID],[ACCNAME],[DETAILED],[DC],[ISFCY],[ISDEPTACC],[ISEMPLACC],[ISCORPACC],[ISITEMACC],[REMARKS],[YEAR],[MONTH],[PACCGID],[ACCATTRIBUTE],[ISENABLE],[ACCTYPE],[STARTGID]) "
+ " select '" + headData.GID.ToString().Trim() + "','" + headData.ACCID.ToString().Trim() + "','" + headData.ACCNAME.ToString().Trim() + "','True',DC,'" + headData.ISFCY.ToString().Trim() + "','" + headData.ISDEPTACC.ToString().Trim() + "','" + headData.ISEMPLACC.ToString().Trim() + "','" + headData.ISCORPACC.ToString().Trim() + "','" + headData.ISITEMACC.ToString().Trim() + "',REMARKS,'" + headData.YEAR.ToString().Trim() + "','" + headData.MONTH.ToString().Trim() + "','" + headData.PACCGID.ToString().Trim() + "',ACCATTRIBUTE,ISENABLE,ACCTYPE,[STARTGID] from cw_accitems_gl WITH(NOLOCK) where gid='" + PGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
//更新凭证明细表，替换旧科目信息
sSQL = "UPDATE [Cw_Vouitems_Gl] SET [ACCID] = '" + headData.ACCID.ToString().Trim() + "',[ACCNAME] = '" + headData.ACCNAME.ToString().Trim() + "',[ACCALL] = replace(ACCALL,'" + cwPACCNAME + "','" + cwPACCNAME + "_" + headData.ACCNAME.ToString().Trim() + "'),[MODIFIEDUSER] = '" + USERID + "',[MODIFIEDTIME] = GETDATE() WHERE ACCID='" + PACCID + "' and ACCNAME='" + PACCNAME + "' and ORDNO in (select ORDNO from cw_vouchers_gl WHERE STARTGID='" + strCwSTARTGID + "' and ACCYEAR=SUBSTRING('" + strCwACCDATE + "',1,4))";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
//更新凭证总账表，替换旧科目信息
sSQL = "UPDATE [cw_genleg_accitems] SET [LINKGID] = '" + headData.GID.ToString().Trim() + "',[MODIFIEDUSER] = '" + USERID + "',[MODIFIEDTIME] = GETDATE() WHERE [STARTGID]='" + strCwSTARTGID + "' and LINKGID='" + PGID + "' and [year]=SUBSTRING('" + strCwACCDATE + "',1,4)";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
#region 更新账套配置表，替换旧科目信息
sSQL = "UPDATE [cw_design_gl] SET [AR] = '" + headData.ACCID.ToString().Trim() + "' WHERE [AR]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [AP] = '" + headData.ACCID.ToString().Trim() + "' WHERE [AP]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [APFC] = '" + headData.ACCID.ToString().Trim() + "' WHERE [APFC]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [MBINCOME] = '" + headData.ACCID.ToString().Trim() + "' WHERE [MBINCOME]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [MBSPENDING] = '" + headData.ACCID.ToString().Trim() + "' WHERE [MBSPENDING]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [TMFCI] = '" + headData.ACCID.ToString().Trim() + "' WHERE [TMFCI]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [TMSFC] = '" + headData.ACCID.ToString().Trim() + "' WHERE [TMSFC]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [CASH] = '" + headData.ACCID.ToString().Trim() + "' WHERE [CASH]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [APMBSPENDING] = '" + headData.ACCID.ToString().Trim() + "' WHERE [APMBSPENDING]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [APTMSFC] = '" + headData.ACCID.ToString().Trim() + "' WHERE [APTMSFC]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [ARMBSPENDING] = '" + headData.ACCID.ToString().Trim() + "' WHERE [ARMBSPENDING]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [ARTMSFC] = '" + headData.ACCID.ToString().Trim() + "' WHERE [ARTMSFC]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [OUTPUTTAX] = '" + headData.ACCID.ToString().Trim() + "' WHERE [OUTPUTTAX]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [INPUTTAX] = '" + headData.ACCID.ToString().Trim() + "' WHERE [INPUTTAX]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [EGOL] = '" + headData.ACCID.ToString().Trim() + "' WHERE [EGOL]='" + PACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
#endregion
//事务提交
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "操作出现错误，请重试!";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
#endregion
#region 事务2
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
string sSQL = "update cw_accitems_gl set DETAILED=0 where GID='" + PGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "INSERT INTO [cw_genleg_accitems]([GID],[YEAR],[MONTH],[LINKGID],[DEPTACC],[EMPLACC],[CORPACC],[ITEMACC],[CURRENCY],[AMTYEARDR],[AMTYEARCR],[AMTYEARBLC],[AMTDR],[AMTCR],[AMTBLC],[AMTLASTDR],[AMTLASTCR],[AMTLASTBLC],[FCYYEARDR],[FCYYEARCR],[FCYYEARBLC],[FCYDR],[FCYCR],[FCYBLC],[FCYLASTDR],[FCYLASTCR],[FCYLASTBLC],[QTYYEARDR],[QTYYEARCR],[QTYYEARBLC],[QTYDR],[QTYCR],[QTYBLC],[QTYLASTDR],[QTYLASTCR],[QTYLASTBLC],[PFADR],[PFACR],[CORPID],[ISDELETE],[DELETEUSER],[DELETETIME],[CREATEUSER],[CREATETIME],[MODIFIEDUSER],[MODIFIEDTIME],[ISENABLE],[ACCDATE],[STARTGID],IsInitialEntry)"
+ " select newid(),[YEAR],[MONTH],'" + PGID + "','','','','',CURRENCY,isnull(sum(AMTYEARDR),0),isnull(sum(AMTYEARCR),0),isnull(sum(AMTYEARBLC),0),isnull(sum(AMTDR),0),isnull(sum(AMTCR),0),isnull(sum(AMTBLC),0),isnull(sum(AMTLASTDR),0),isnull(sum(AMTLASTCR),0),isnull(sum(AMTLASTBLC),0),isnull(sum(FCYYEARDR),0),isnull(sum(FCYYEARCR),0),isnull(sum(FCYYEARBLC),0),isnull(sum(FCYDR),0),isnull(sum(FCYCR),0),isnull(sum(FCYBLC),0),isnull(sum(FCYLASTDR),0),isnull(sum(FCYLASTCR),0),isnull(sum(FCYLASTBLC),0),isnull(sum(QTYYEARDR),0),isnull(sum(QTYYEARCR),0),isnull(sum(QTYYEARBLC),0),isnull(sum(QTYDR),0),isnull(sum(QTYCR),0),isnull(sum(QTYBLC),0),isnull(sum(QTYLASTDR),0),isnull(sum(QTYLASTCR),0),isnull(sum(QTYLASTBLC),0),isnull(sum(PFADR),0),isnull(sum(PFACR),0),CORPID,'False','" + USERID + "',GETDATE(),'" + USERID + "',GETDATE(),'" + USERID + "',GETDATE(),ISENABLE,([YEAR]+'-'+[MONTH]),STARTGID,IsInitialEntry"
+ " from [cw_genleg_accitems] WITH(NOLOCK) where [STARTGID]='" + strCwSTARTGID + "' and [LINKGID]='" + headData.GID.ToString().Trim() + "' and [year]=SUBSTRING('" + strCwACCDATE + "',1,4) group by [YEAR],[MONTH],CURRENCY,CORPID,ISENABLE,STARTGID,IsInitialEntry";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
//事务提交
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "操作出现错误，请重试!";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
#endregion
result.Success = true;
result.Message = "操作成功";
return result;
}
#endregion
#region 科目代码或名称变更，则替换数据库中所有涉及的数据
public static DBResult updateName(string newACCID, string oldACCID, string newACCNAME, string oldACCNAME, MsCwAccitemsGl headData, string USERID)
{
#region 默认值
var result = new DBResult();
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(USERID);
string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(USERID);
//提取新旧科目名称（父类科目+子科目）
string cwPACCGID = headData.PACCGID.ToString().Trim();
string oldPACCNAME = oldACCNAME;
string newPACCNAME = newACCNAME;
if (cwPACCGID != "ZC" && cwPACCGID != "FZ" && cwPACCGID != "GT" && cwPACCGID != "QY" && cwPACCGID != "CB" && cwPACCGID != "SY")
{
oldPACCNAME = BasicDataRefDAL.setCAGA(cwPACCGID, oldACCNAME);
newPACCNAME = BasicDataRefDAL.setCAGA(cwPACCGID, newACCNAME);
}
#endregion
#region 事务
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
//更新凭证明细表，替换旧科目信息
string sSQL = "UPDATE [Cw_Vouitems_Gl] SET [ACCID] = '" + newACCID + "',[ACCNAME] = '" + newACCNAME + "',[ACCALL] = replace(ACCALL,'" + oldPACCNAME + "','" + newPACCNAME + "'),[MODIFIEDUSER] = '" + USERID + "',[MODIFIEDTIME] = GETDATE() WHERE ACCID='" + oldACCID + "' and ACCNAME='" + oldACCNAME + "' and ORDNO in (select ORDNO from cw_vouchers_gl WHERE STARTGID='" + strCwSTARTGID + "' and ACCYEAR=SUBSTRING('" + strCwACCDATE + "',1,4))";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
#region 更新账套配置表，替换旧科目信息
sSQL = "UPDATE [cw_design_gl] SET [AR] = '" + newACCID + "' WHERE [AR]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [AP] = '" + newACCID + "' WHERE [AP]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [APFC] = '" + newACCID + "' WHERE [APFC]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [MBINCOME] = '" + newACCID + "' WHERE [MBINCOME]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [MBSPENDING] = '" + newACCID + "' WHERE [MBSPENDING]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [TMFCI] = '" + newACCID + "' WHERE [TMFCI]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [TMSFC] = '" + newACCID + "' WHERE [TMSFC]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [CASH] = '" + newACCID + "' WHERE [CASH]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [APMBSPENDING] = '" + newACCID + "' WHERE [APMBSPENDING]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [APTMSFC] = '" + newACCID + "' WHERE [APTMSFC]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [ARMBSPENDING] = '" + newACCID + "' WHERE [ARMBSPENDING]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [ARTMSFC] = '" + newACCID + "' WHERE [ARTMSFC]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [OUTPUTTAX] = '" + newACCID + "' WHERE [OUTPUTTAX]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [INPUTTAX] = '" + newACCID + "' WHERE [INPUTTAX]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
sSQL = "UPDATE [cw_design_gl] SET [EGOL] = '" + newACCID + "' WHERE [EGOL]='" + oldACCID + "' and [STARTGID]='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
#endregion
//事务提交
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "操作出现错误，请重试!";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
#endregion
result.Success = true;
result.Message = "操作成功";
return result;
}
#endregion
#region 如果科目类型变更，则替换此子科目的科目类型
public static DBResult updateZiKeMu(string sPACCGID, Boolean ISBANK, Boolean ISSETTLEMENT, string USERID)
{
#region 默认值
var result = new DBResult();
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(USERID);
string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(USERID);
#endregion
#region 事务
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
//替换此子科目的科目类型
string sSQL = "UPDATE [cw_accitems_gl] SET [ISBANK]='" + ISBANK + "',[ISSETTLEMENT]='" + ISSETTLEMENT + "' WHERE PACCGID='" + sPACCGID + "' and STARTGID='" + strCwSTARTGID + "'";
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSQL, null);
//事务提交
sqlTran.Commit();
}
catch (Exception)
{
sqlTran.Rollback();
result.Success = false;
result.Message = "操作出现错误，请重试!";
return result;
}
finally
{
SqlHelper.CloseConnection();
sqlTran.Dispose();
}
}
#endregion
result.Success = true;
result.Message = "操作成功";
return result;
}
#endregion
#region 科目是否已经使用
public static string GetIsDisable(string sACCID, string strUSERID)
{
string strCwACCDATE = BasicDataRefDAL.GetCwACCDATE(strUSERID);
string strCwSTARTGID = BasicDataRefDAL.GetCwSTARTGID(strUSERID);
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
string alt = "false";
string sSQL = "select top 1 * from cw_accitems_gl where [year]=SUBSTRING('" + strCwACCDATE + "',1,4) and [STARTGID]='" + strCwSTARTGID + "' and ACCID='" + sACCID + "'";
DataSet ds = T_ALL_DA.GetAllSQL(sSQL);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
//获取参数
SqlParameter[] parms = new SqlParameter[]
{
new SqlParameter("@GID",SqlDbType.VarChar,36),
new SqlParameter("@ACCID",SqlDbType.VarChar,16),
new SqlParameter("@ACCNAME",SqlDbType.VarChar,60)
};
parms[0].Value = ds.Tables[0].Rows[0]["GID"].ToString().Trim();
parms[1].Value = ds.Tables[0].Rows[0]["ACCID"].ToString().Trim();
parms[2].Value = ds.Tables[0].Rows[0]["ACCNAME"].ToString().Trim();
//
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
{
int iResult = SqlHelper.ExecuteSqlStoredProcedureReturn(conn, "proc_isCwAccitemsBeing", parms);
if (iResult == 1)
{
alt = "true";//"在相关业务模块中已经引用，禁止删除此数据！";
}
else
{
alt = "false";
}
}
}
}
return alt.Trim();
}
#endregion
//
}
}