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); }
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; }