internal bool InsertData(Model_凭证单 Voucher, List <Model_凭证明细> VoucherDetails)
{
bool isEmpty = true;
decimal CountBorrow = 0m;
decimal CountLoan = 0m;
List <Model_凭证明细> NewVoucherDetails = new List <Model_凭证明细>();
foreach (Model_凭证明细 VoucherDetail in VoucherDetails)
{
if (VoucherDetail.科目编号 != null)
{
NewVoucherDetails.Add(VoucherDetail);
CountBorrow += VoucherDetail.借方;
CountLoan += VoucherDetail.贷方;
isEmpty = false;
}
}
if (!isEmpty)
{
if (CountBorrow != CountLoan)
{
return(false);
}
List <Model_凭证单> Vouchers = new List <Model_凭证单>();
Vouchers.Add(Voucher);
bool result = new PA.Helper.DataBase.DataBase().InsertVoucherAll(Vouchers.OfType <object>().ToList(), NewVoucherDetails.OfType <object>().ToList());
if (!result)
{
return(false);
}
}
return(!isEmpty);
}
private void Update()
{
string sql = "";
bool flag = Read(out sql);
flag = new PA.Helper.DataBase.DataBase().Excute(sql);
if (flag)
{
File.Delete(UpdateFilePath);
}
}
internal int GetPageNum(Guid guid)
{
int result = 1;
string sql = "select count(*) from (SELECT count(*) FROM " + DBTablesName.T_VOUCHER_DETAIL + " WHERE PARENTID = '" + guid + "' GROUP BY VOUCHER_NO)";
DataSet ds = new PA.Helper.DataBase.DataBase().Query(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
result = int.Parse(dr[0].ToString());
}
return(result);
}
internal Model_凭证单 GetVoucher(Guid guid)
{
Model_凭证单 Voucher = new Model_凭证单();
string sql = "select * from " + DBTablesName.T_VOUCHER + " where id='" + guid + "'";
DataSet ds = new PA.Helper.DataBase.DataBase().Query(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Voucher.ID = dr[0].ToString();
Voucher.当前期 = int.Parse(dr[1].ToString());
Voucher.制表时间 = Convert.ToDateTime(dr[2]);
Voucher.附属单证数 = int.Parse(dr[3].ToString());
Voucher.合计借方金额 = decimal.Parse(dr[4].ToString());
Voucher.合计贷方金额 = decimal.Parse(dr[5].ToString());
Voucher.会计主管 = dr[6].ToString();
Voucher.制单人 = dr[7].ToString();
Voucher.核 = dr[8].ToString();
Voucher.审核标志 = int.Parse(dr[9].ToString());
Voucher.除标志 = int.Parse(dr[10].ToString());
}
return(Voucher);
}
internal List <Model_凭证明细> GetVoucherDetails(Guid guid)
{
string LastVoucherNum = "";
int CountNum = 0;
bool isFirstLine = true;
List <Model_凭证明细> VoucherDetails = new List <Model_凭证明细>();
Model_凭证明细 detail;
string sql = "select detail.*, subjectA.subject_name as MainSubjectName, subjectB.subject_name as TimesSubjectName"
+ " from "
+ DBTablesName.T_VOUCHER_DETAIL + " detail"
+ " LEFT JOIN " + DBTablesName.T_SUBJECT + " subjectA ON detail.subject_id=subjectA.subject_id"
+ " LEFT JOIN " + DBTablesName.T_SUBJECT + " subjectB ON detail.detail=subjectB.subject_id"
+ " where PARENTID='" + guid + "'";
DataSet ds = new PA.Helper.DataBase.DataBase().Query(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (LastVoucherNum == dr[4].ToString() || isFirstLine)
{
detail = new Model_凭证明细();
detail.ID = int.Parse(dr[0].ToString());
detail.序号 = int.Parse(dr[1].ToString());
detail.父节点ID = dr[2].ToString();
detail.凭证字 = dr[3].ToString();
detail.凭证号 = dr[4].ToString();
detail.摘要 = dr[5].ToString();
detail.科目编号 = dr[6].ToString();
detail.子细目ID = dr[7].ToString();
detail.记账 = int.Parse(dr[8].ToString());
detail.借方 = decimal.Parse(dr[9].ToString());
detail.贷方 = decimal.Parse(dr[10].ToString());
detail.主科目名 = dr[6].ToString() + " " + dr["MainSubjectName"].ToString();
detail.子细目 = dr[7].ToString() + " " + dr["TimesSubjectName"].ToString();
VoucherDetails.Add(detail);
LastVoucherNum = dr[4].ToString();
CountNum++;
isFirstLine = false;
}
else
{
//补全上一页剩余空白行
for (int i = CountNum; i < 6; i++)
{
detail = new Model_凭证明细();
detail.序号 = i;
VoucherDetails.Add(detail);
}
CountNum = 0;
detail = new Model_凭证明细();
detail.ID = int.Parse(dr[0].ToString());
detail.序号 = int.Parse(dr[1].ToString());
detail.父节点ID = dr[2].ToString();
detail.凭证字 = dr[3].ToString();
detail.凭证号 = dr[4].ToString();
detail.摘要 = dr[5].ToString();
detail.科目编号 = dr[6].ToString();
detail.子细目ID = dr[7].ToString();
detail.记账 = int.Parse(dr[8].ToString());
detail.借方 = decimal.Parse(dr[9].ToString());
detail.贷方 = decimal.Parse(dr[10].ToString());
detail.主科目名 = dr[6].ToString() + " " + dr["MainSubjectName"].ToString();
detail.子细目 = dr[7].ToString() + " " + dr["TimesSubjectName"].ToString();
VoucherDetails.Add(detail);
LastVoucherNum = dr[4].ToString();
CountNum++;
}
}
//补全最后的空白行
for (int i = CountNum; i < 6; i++)
{
detail = new Model_凭证明细();
detail.序号 = i;
VoucherDetails.Add(detail);
}
return(VoucherDetails);
}
/// <summary>
/// 不显示三级科目
/// </summary>
/// <param name="condition"></param>
/// <param name="id"></param>
/// <param name="isTwo"></param>
/// <returns></returns>
public List<string> GetChildSubjectList(string condition, string id, bool isTwo)
{
list = new List<string>();
string sql1 = string.Empty;
string sql2 = string.Empty;
sql = "select subject_id,subject_name from " + DBTablesName.T_SUBJECT;
if (isTwo)
{
sql1 = " where parent_id = '" + id + "'";
}
else
{
sql1 = " where parent_id like '" + id + "%'";
}
if (!string.IsNullOrEmpty(condition))
{
sql2 = " and subject_id like '" + condition + "%'";
}
sql += sql1 + sql2 + " order by subject_id";
DataBase db = new DataBase();
DataTable dt = db.Query(sql).Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
string str = dt.Rows[i][0] + "\t" + dt.Rows[i][1];
list.Add(str);
}
return list;
}
/// <summary>
/// 获取所有科目的最低级科目（子细目）
/// </summary>
/// <returns></returns>
public List<string> GetChildSubjectList(string condition)
{
List<string> lists = new List<string>();
string parm = string.Empty;
if (!string.IsNullOrEmpty(condition))
{
parm = " and a.subject_id like '" + condition + "%'";
}
string sql = " SELECT "
+ " a.SUBJECT_ID, "
+ " a.subject_name, "
+ " b.SUBJECT_ID ParentID, "
+ " b.subject_name ParentName, "
+ " c.SUBJECT_ID ParentID, "
+ " c.subject_name ParentName, "
+ " d.SUBJECT_ID ParentID, "
+ " d.subject_name ParentName "
+ " FROM "
+ DBTablesName.T_SUBJECT
+ " a LEFT JOIN "
+ DBTablesName.T_SUBJECT
+ " b ON substr(a.SUBJECT_ID, 0, 4) = b.SUBJECT_ID "
+ " LEFT JOIN "
+ DBTablesName.T_SUBJECT
+ " c ON substr(a.SUBJECT_ID, 0, 6) = c.SUBJECT_ID "
+ " LEFT JOIN "
+ DBTablesName.T_SUBJECT
+ " d ON substr(a.SUBJECT_ID, 0, 8) = d.SUBJECT_ID "
+ " WHERE "
+ " a.SUBJECT_ID NOT IN ( "
+ " SELECT "
+ " PARENT_ID "
+ " FROM "
+ " T_SUBJECT_0 "
+ " GROUP BY "
+ " PARENT_ID "
+ " ) "
+ parm
+ " ORDER BY "
+ " a.SUBJECT_ID ";
DataBase db = new DataBase();
DataTable dt = db.Query(sql).Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
string str = dt.Rows[i][0] + "\t"
+ dt.Rows[i][1] + "\t" //name
+ dt.Rows[i][2] + "\t"
+ dt.Rows[i][3] + "\t" //name
+ dt.Rows[i][4] + "\t"
+ dt.Rows[i][5] + "\t" //name
+ dt.Rows[i][6] + "\t"
+ dt.Rows[i][7]; //name
lists.Add(str);
}
return lists;
}
public List<string> GetSubjectList(string condition)
{
list = new List<string>();
if (string.IsNullOrEmpty(condition))
{
sql = "select subject_id,subject_name from " + DBTablesName.T_SUBJECT + " where used_mark=0 and parent_id=0 order by id";
}
else
{
sql = "select subject_id,subject_name from " + DBTablesName.T_SUBJECT + " where used_mark=0 " + "and subject_id like '" + condition +
"%' and parent_id=0 order by id";
}
DataBase db = new DataBase();
DataTable dt = db.Query(sql).Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
string str = dt.Rows[i][0]+"\t"+dt.Rows[i][1];
list.Add(str);
}
return list;
}
private void DataInitialize()
{
DataBase db = new DataBase();
List<string> dataList = new List<string>();
dataList = getSqlList(Properties.Resources.DatabaseTable);
db.BatchOperate(dataList);
dataList.Clear();
dataList = GetSubjectSqlList();
db.BatchOperate(dataList);
//插入数据
dataList.Clear();
dataList = getSqlList(Properties.Resources.DatabaseData);
string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
//获取U盘唯一码
UsbController usb = new UsbController();
string _str = usb.getSerialNumberFromDriveLetter();
string sql = "insert into t_systeminfo (op_time,rkey,value,comments) values ('" + date
+ "','" + (int)ENUM.EM_KEY.软件版本 + "','" + (int)ENUM.EM_SOFTWARESTATE.未注册 + "','第一次运行显示未注册'),('" + date
+ "','" + (int)ENUM.EM_KEY.注册码 + "','','注册码'),('" + date
+ "','" + (int)ENUM.EM_KEY.U盘标识 + "','" + _str + "','USB')";
dataList.Add(sql);
db.BatchOperate(dataList);
}
private void Update()
{
string sql = "";
bool flag = Read(out sql);
flag = new PA.Helper.DataBase.DataBase().Excute(sql);
if (flag)
{
File.Delete(UpdateFilePath);
}
}
internal bool InsertData(Model_凭证单 Voucher, List<Model_凭证明细> VoucherDetails)
{
bool isEmpty = true;
decimal CountBorrow = 0m;
decimal CountLoan = 0m;
List<Model_凭证明细> NewVoucherDetails = new List<Model_凭证明细>();
foreach (Model_凭证明细 VoucherDetail in VoucherDetails)
{
if (VoucherDetail.科目编号 != null)
{
NewVoucherDetails.Add(VoucherDetail);
CountBorrow += VoucherDetail.借方;
CountLoan += VoucherDetail.贷方;
isEmpty = false;
}
}
if (!isEmpty)
{
if (CountBorrow != CountLoan)
{
return false;
}
List<Model_凭证单> Vouchers = new List<Model_凭证单>();
Vouchers.Add(Voucher);
bool result = new PA.Helper.DataBase.DataBase().InsertVoucherAll(Vouchers.OfType<object>().ToList(), NewVoucherDetails.OfType<object>().ToList());
if(!result)
{
return false;
}
}
return !isEmpty;
}
internal List<Model_凭证明细> GetVoucherDetails(Guid guid)
{
string LastVoucherNum = "";
int CountNum = 0;
bool isFirstLine = true;
List<Model_凭证明细> VoucherDetails = new List<Model_凭证明细>();
Model_凭证明细 detail;
string sql = "select detail.*, subjectA.subject_name as MainSubjectName, subjectB.subject_name as TimesSubjectName"
+ " from "
+ DBTablesName.T_VOUCHER_DETAIL + " detail"
+ " LEFT JOIN " + DBTablesName.T_SUBJECT + " subjectA ON detail.subject_id=subjectA.subject_id"
+ " LEFT JOIN " + DBTablesName.T_SUBJECT + " subjectB ON detail.detail=subjectB.subject_id"
+ " where PARENTID='" + guid + "'";
DataSet ds = new PA.Helper.DataBase.DataBase().Query(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (LastVoucherNum == dr[4].ToString() || isFirstLine)
{
detail = new Model_凭证明细();
detail.ID = int.Parse(dr[0].ToString());
detail.序号 = int.Parse(dr[1].ToString());
detail.父节点ID = dr[2].ToString();
detail.凭证字 = dr[3].ToString();
detail.凭证号 = dr[4].ToString();
detail.摘要 = dr[5].ToString();
detail.科目编号 = dr[6].ToString();
detail.子细目ID = dr[7].ToString();
detail.记账 = int.Parse(dr[8].ToString());
detail.借方 = decimal.Parse(dr[9].ToString());
detail.贷方 = decimal.Parse(dr[10].ToString());
detail.主科目名 = dr[6].ToString()+ " " +dr["MainSubjectName"].ToString();
detail.子细目 = dr[7].ToString() + " " + dr["TimesSubjectName"].ToString();
VoucherDetails.Add(detail);
LastVoucherNum = dr[4].ToString();
CountNum++;
isFirstLine = false;
}
else
{
//补全上一页剩余空白行
for (int i = CountNum; i < 6; i++)
{
detail = new Model_凭证明细();
detail.序号 = i;
VoucherDetails.Add(detail);
}
CountNum = 0;
detail = new Model_凭证明细();
detail.ID = int.Parse(dr[0].ToString());
detail.序号 = int.Parse(dr[1].ToString());
detail.父节点ID = dr[2].ToString();
detail.凭证字 = dr[3].ToString();
detail.凭证号 = dr[4].ToString();
detail.摘要 = dr[5].ToString();
detail.科目编号 = dr[6].ToString();
detail.子细目ID = dr[7].ToString();
detail.记账 = int.Parse(dr[8].ToString());
detail.借方 = decimal.Parse(dr[9].ToString());
detail.贷方 = decimal.Parse(dr[10].ToString());
detail.主科目名 = dr[6].ToString()+ " " +dr["MainSubjectName"].ToString();
detail.子细目 = dr[7].ToString() + " " + dr["TimesSubjectName"].ToString();
VoucherDetails.Add(detail);
LastVoucherNum = dr[4].ToString();
CountNum++;
}
}
//补全最后的空白行
for (int i = CountNum; i < 6; i++)
{
detail = new Model_凭证明细();
detail.序号 = i;
VoucherDetails.Add(detail);
}
return VoucherDetails;
}
internal Model_凭证单 GetVoucher(Guid guid)
{
Model_凭证单 Voucher = new Model_凭证单();
string sql = "select * from " + DBTablesName.T_VOUCHER + " where id='" + guid + "'";
DataSet ds = new PA.Helper.DataBase.DataBase().Query(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Voucher.ID = dr[0].ToString();
Voucher.当前期 = int.Parse(dr[1].ToString());
Voucher.制表时间 = Convert.ToDateTime(dr[2]);
Voucher.附属单证数 = int.Parse(dr[3].ToString());
Voucher.合计借方金额 = decimal.Parse(dr[4].ToString());
Voucher.合计贷方金额 = decimal.Parse(dr[5].ToString());
Voucher.会计主管 = dr[6].ToString();
Voucher.制单人 = dr[7].ToString();
Voucher.复核 = dr[8].ToString();
Voucher.审核标志 = int.Parse(dr[9].ToString());
Voucher.删除标志 = int.Parse(dr[10].ToString());
}
return Voucher;
}
internal int GetPageNum(Guid guid)
{
int result = 1;
string sql = "select count(*) from (SELECT count(*) FROM " + DBTablesName.T_VOUCHER_DETAIL + " WHERE PARENTID = '"+ guid +"' GROUP BY VOUCHER_NO)";
DataSet ds = new PA.Helper.DataBase.DataBase().Query(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
result = int.Parse(dr[0].ToString());
}
return result;
}