public List <User> getAllUserByLoginName(string where) { string sql = "select ID,loginName from tb_User where 1=1 " + where + " order by loginName"; List <User> list = new List <User>(); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); SqlCommand objCommand = new SqlCommand(sql, conn); using (SqlDataReader objReader = objCommand.ExecuteReader()) { while (objReader.Read()) { User User = new User(); User.Id = objReader.GetInt32(0); User.LoginName = objReader.GetString(1); list.Add(User); } objReader.Close(); } } return(list); }
private void LastUpdate(int id) { CAI_OrderInHousesService ordersSer = new CAI_OrderInHousesService(); List <CAI_OrderInHouses> orders = ordersSer.GetListArray(" 1=1 and CAI_OrderInHouses.id=1"); CAI_OrderChecksService ordersSer111 = new CAI_OrderChecksService(); List <CAI_OrderChecks> orders1111 = ordersSer111.GetListArray(" 1=1 and CAI_OrderChecks.CheckId=" + id); CAI_OrderCheckService mainSer = new CAI_OrderCheckService(); CAI_OrderCheck pp = mainSer.GetModel(id); DBHelp.ExeCommand(string.Format(@" update CAI_OrderInHouse SET ChcEkProNo='{0}',pono='{1}',poname='{2}' where id=1; update CAI_OrderOutHouse set pono='{1}',poname='{2}' where ChcEkProNo='20120000'; update CAI_OrderCheck set status='通过' where id={3} ;update tb_EForm set state='通过',e_LastTime=getdate() where allE_id={3} and proId=21;", pp.ProNo, orders1111[0].PONo, orders1111[0].POName, id)); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); SqlTransaction tan = conn.BeginTransaction(); SqlCommand objCommand = conn.CreateCommand(); objCommand.Transaction = tan; CAI_POCaiService CaiSer = new CAI_POCaiService(); try { objCommand.Parameters.Clear(); foreach (var caiIn in orders) { var check = orders1111.Single(t => t.CheckGoodId == caiIn.GooId); string sql = string.Format(" update CAI_OrderInHouses set OrderCheckIds={1} where ids={0}", caiIn.Ids, check.Ids); objCommand.CommandText = sql; objCommand.ExecuteNonQuery(); } tan.Commit(); } catch (Exception) { tan.Rollback(); } } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); var selectInfo = string.Format(@" select Id,DisDate,OutDispater from tb_Dispatching where MyXiShu=0 and DisDate>='2018-2-22' "); SqlCommand objCommand = conn.CreateCommand(); objCommand.CommandText = selectInfo; List <tb_Dispatching> idsList = new List <tb_Dispatching>(); using (var reader = objCommand.ExecuteReader()) { while (reader.Read()) { tb_Dispatching model = new tb_Dispatching(); model.Id = Convert.ToInt32(reader["Id"]); model.DisDate = Convert.ToDateTime(reader["DisDate"]); model.OutDispater = Convert.ToInt32(reader["OutDispater"]); idsList.Add(model); } reader.Close(); } foreach (var m in idsList) { string sql = string.Format(@"select h.ShouldPayment FROM HR_Payment H right join HR_Person P on H.ID=P.ID where P.ID='{0}' and H.YearMonth='{1}'", m.OutDispater, m.DisDate.Value.AddMonths(-2).Year + "-" + m.DisDate.Value.AddMonths(-2).Month.ToString("00")); objCommand.CommandText = sql; var val = objCommand.ExecuteScalar(); decimal xishu = 0; if (val != null && !(val is DBNull)) { xishu = Convert.ToDecimal(val) / 3000; } objCommand.CommandText = string.Format("update tb_Dispatching set MyXiShu={1} where id={0}", m.Id, xishu); objCommand.ExecuteNonQuery(); } } } }
protected void Button1_Click(object sender, EventArgs e) { List <int> ruids = new List <int>(); ruids.Add(9917); //ruids.Add(14294); //ruids.Add(14312); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); foreach (var ids in ruids) { var selectInfo = string.Format(@"select avg(SupplierInvoiceNum) as avgSupplierInvoiceNum, sum(SupplierInvoiceTotal) as sumSupplierInvoiceTotal from TB_SupplierInvoices left join TB_SupplierInvoice on TB_SupplierInvoice.id=TB_SupplierInvoices.id where RuIds={0} and SupplierInvoiceTotal>0 and ((status='通过' and IsYuFu=0) or (Status<>'不通过' and IsYuFu=1)) ", ids); SqlCommand objCommand = conn.CreateCommand(); objCommand.CommandText = selectInfo; decimal avgSupplierInvoiceNum = 0; decimal sumSupplierInvoiceTotal = 0; using (var reader = objCommand.ExecuteReader()) { if (reader.Read()) { object ojb; ojb = reader["avgSupplierInvoiceNum"]; if (ojb != null && ojb != DBNull.Value) { avgSupplierInvoiceNum = Convert.ToDecimal(ojb); } ojb = reader["sumSupplierInvoiceTotal"]; if (ojb != null && ojb != DBNull.Value) { sumSupplierInvoiceTotal = Convert.ToDecimal(ojb); } } reader.Close(); } TB_SupplierInvoice model = new TB_SupplierInvoice(); model.Status = "通过"; model.CreteTime = DateTime.Now; model.CreateName = "姚世中"; model.CaiTuiProNo = ""; model.LastSupplier = "易佳通"; model.Remark = "全部退货"; var eform = new tb_EForm(); eform.appPer = 117; eform.appTime = DateTime.Now; eform.createPer = 117; eform.createTime = DateTime.Now; eform.proId = 31; eform.state = "通过"; eform.toPer = 0; eform.toProsId = 0; var orders = new List <SupplierToInvoiceView>(); var invoiceModel = new SupplierToInvoiceView(); invoiceModel.Ids = ids; //invoiceModel.IsYuFu = true; invoiceModel.SupplierFPNo = ""; invoiceModel.SupplierInvoiceDate = DateTime.Now; var AvgSupplierInvoicePrice = -(sumSupplierInvoiceTotal / avgSupplierInvoiceNum); invoiceModel.SupplierInvoicePrice = AvgSupplierInvoicePrice; invoiceModel.lastGoodNum = avgSupplierInvoiceNum; invoiceModel.SupplierInvoiceTotal = -sumSupplierInvoiceTotal; invoiceModel.FuShuTotal = invoiceModel.SupplierInvoiceTotal; invoiceModel.ActPay = invoiceModel.SupplierInvoiceTotal; invoiceModel.GuestName = "易佳通"; //invoiceModel.CaiTuiProNo = caiTuiProNo; invoiceModel.RePayClear = 2; invoiceModel.IsPayStatus = 0;//未支付 invoiceModel.IsHeBing = 1; orders.Add(invoiceModel); int mainId = 0; new TB_SupplierInvoiceService().addTran(model, eform, orders, out mainId); } } }
private void LoadExcel(string fileAddress) { string strConn; strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileAddress + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); int allSheets = 0; foreach (DataRow rowTable in sheetNames.Rows) { string sheetName = rowTable["TABLE_NAME"].ToString(); conn = new OleDbConnection(strConn); conn.Open(); OleDbCommand objCommand = new OleDbCommand(string.Format("select * from [" + sheetName + "]"), conn); int rowIndex = 1; BankFlowService bandFlowService = new BankFlowService(); PetitionsService petSer = new PetitionsService(); CG_POOrderService POSer = new CG_POOrderService(); CAI_POCaiService pocaiSer = new CAI_POCaiService(); using (SqlConnection sqlconn = DBHelp.getConn()) { sqlconn.Open(); SqlCommand sqlCommand = sqlconn.CreateCommand(); using (OleDbDataReader dataReader = objCommand.ExecuteReader()) { while (dataReader.Read()) { //if (rowIndex > 1) //{ // Petitions model = new Petitions(); // model.Number = dataReader[0].ToString(); // model.Type = dataReader[1].ToString(); // model.OldIndex = dataReader[2].ToString(); // var ojb = dataReader[3]; // if (ojb != null && ojb != DBNull.Value) // { // model.SignDate = Convert.ToDateTime(ojb); // } // model.PoNo = dataReader[4].ToString(); // model.Summary = dataReader[5].ToString(); // ojb = dataReader[6]; // if (ojb != null && ojb != DBNull.Value) // { // model.SumPages = Convert.ToInt32(ojb); // } // model.Local = dataReader[7].ToString(); // model.L_Year = Convert.ToInt32(dataReader[8]); // model.L_Month = Convert.ToInt32(dataReader[9]); // model.Remark = dataReader[10].ToString(); // model.SumCount = 1; // model.BCount = 1; // model.Handler = "王汉中"; // var poList = POSer.GetSimpListArray(string.Format("and pono='{0}'", model.PoNo), sqlCommand); // if (poList.Count > 0) // { // CG_POOrder poorderModel = poList[0]; // model.AE = poorderModel.AE; // model.GuestName = poorderModel.GuestName; // model.Name = poorderModel.POName; // //var supplierList = pocaiSer.GetLastSupplier(string.Format("'{0}'", model.PoNo), sqlCommand); // //model.SalesUnit = string.Join(",", supplierList.Select(t => t.Supplier).ToArray()); // //model.Total = supplierList.Sum(t => t.Total); // } // petSer.Add(model,sqlCommand); //} if (rowIndex > 8) { BankFlow model = new BankFlow(); model.TransactionType = dataReader[0].ToString(); if (model.TransactionType == "") { continue; } model.BusinessType = dataReader[1].ToString(); model.PayerAccountBank = dataReader[3].ToString(); model.DebitAccountNo = dataReader[4].ToString(); model.OutPayerName = dataReader[5].ToString(); model.BeneficiaryAccountBank = dataReader[7].ToString(); model.PayeeAccountNumber = dataReader[8].ToString(); model.InPayeeName = dataReader[9].ToString(); DateTime dt = DateTime.ParseExact(dataReader[10].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture); model.TransactionDate = Convert.ToDateTime(dt.ToString("yyyy-MM-dd") + " " + dataReader[11]); model.TradeCurrency = dataReader[12].ToString(); model.TradeAmount = Convert.ToDecimal(dataReader[13]); model.AfterTransactionBalance = Convert.ToDecimal(dataReader[14]); model.TransactionReferenceNumber = dataReader[17].ToString(); model.VoucherType = dataReader[20].ToString(); model.Reference = dataReader[23].ToString(); model.Purpose = dataReader[24].ToString(); model.Remark = dataReader[25].ToString(); model.Remarks = dataReader[26].ToString(); //判断是否重复 string sql = string.Format("SELECT top 1 ID FROM [BankFlow] WHERE TransactionReferenceNumber='{0}'", model.TransactionReferenceNumber); sqlCommand.CommandText = sql; var resultId = sqlCommand.ExecuteScalar(); if (!(resultId is DBNull) && resultId != null) { model.Id = Convert.ToInt32(resultId); bandFlowService.Update(model, sqlCommand); } else { bandFlowService.Add(model, sqlCommand); } } rowIndex++; } } sqlconn.Close(); } } }
public void Execute() { //OA系统每天晚上23:55,准时发起,一个任务,就是点击缓存按钮的动作,这样我们可以获得 当天的数据的快速列表印象。 if (DateTime.Now.Hour == 22 && DateTime.Now.Minute == 00) { ServiceAppSetting.LoggerHander.Invoke(string.Format("执行-缓存! - {0}", DateTime.Now), ""); new JXC_REPORTService().CatchData(); ServiceAppSetting.LoggerHander.Invoke(string.Format("结束-缓存! - {0}", DateTime.Now), ""); } ServiceAppSetting.LoggerHander.Invoke(string.Format("do! {0}", DateTime.Now), ""); //每天早上1,2,3点处理最高值 if ((DateTime.Now.Hour == 1 || DateTime.Now.Hour == 2 || DateTime.Now.Hour == 3) && DateTime.Now.Minute == 0) { ServiceAppSetting.LoggerHander.Invoke(string.Format("执行-最高金额! - {0}", DateTime.Now), ""); CashFlowService cashFlowService = new CashFlowService(); List <CashFlow> cashFlowList = cashFlowService.GetListArray(" AND IsClose=0"); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); SqlCommand objCommand = conn.CreateCommand(); try { objCommand.Parameters.Clear(); foreach (var model in cashFlowList) { cashFlowService.Update(model, objCommand); } } catch (Exception) { } } ServiceAppSetting.LoggerHander.Invoke(string.Format("结束-最高金额! - {0}", DateTime.Now), ""); } if (DateTime.Now.Hour == 23 && DateTime.Now.Minute <= 5) { ServiceAppSetting.LoggerHander.Invoke(string.Format("执行一次! - {0}", DateTime.Now), ""); int currentYear = DateTime.Now.Year; int currentMonth = DateTime.Now.Month; int nextYear = DateTime.Now.AddMonths(1).Year; int nextMonth = DateTime.Now.AddMonths(1).Month; int currentZhangQi = GetZhouQi(currentMonth); int nextZhangQi = GetZhouQi(nextMonth); //if (currentZhangQi == nextZhangQi && currentYear == nextYear) //{ // return; //} List <GuestProBaseInfo> guestProBaseInfos = new GuestProBaseInfoService().GetListArray("GuestPro=1"); TB_GuestTrackService GuestTrackSer = new TB_GuestTrackService(); string sql = string.Format(" 1=1 and QuartNo='{1}' and YearNo='{0}' ", currentYear, currentZhangQi); sql += string.Format(@" and (TB_GuestTrack.id in (select allE_id from tb_EForm where proId in ( select pro_Id from A_ProInfo where pro_Type='客户联系跟踪表') and state='通过') or TB_GuestTrack.id not in (select allE_id from tb_EForm where proId in ( select pro_Id from A_ProInfo where pro_Type='客户联系跟踪表') ))"); List <TB_GuestTrack> GuestTracks = GuestTrackSer.GetListArray(sql); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); SqlTransaction tan = conn.BeginTransaction(); SqlCommand objCommand = conn.CreateCommand(); objCommand.Transaction = tan; try { objCommand.Parameters.Clear(); foreach (var model in GuestTracks) { //if (currentZhangQi == 4) //{ // if (model.MyGuestProString == "自我开拓") // { // model.MyGuestPro = 2; // } //} model.QuartNo = nextZhangQi.ToString(); model.YearNo = nextYear.ToString(); model.CreateTime = DateTime.Now; string secondUpdate = GuestTrackSer.UpdateToString(model, model.GuestId, nextYear.ToString(), nextZhangQi.ToString()); if (model.MyGuestProString == "自我开拓") { //我们的系统在每个季度的最后一天的晚上12点会自动同步客户信息到下一个季度,我们定义 //. 如该客户目前的属性是自我开拓,该客户从登记之日起,加上自我开拓的有效期月数数字对应的日期, //如小于(<) 新季度第一天,新季度的客户的属性变成 原有资源,否则 还是自我开拓 if (DateTime.Now.Month == 3 || DateTime.Now.Month == 6 || DateTime.Now.Month == 9 || DateTime.Now.Month == 12) { if (DateTime.Now.Day == DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month) && model.Time.AddMonths(guestProBaseInfos[0].GuestMonth) < Convert.ToDateTime(nextYear + "-" + nextMonth + "-01")) { model.MyGuestPro = 2; } } } string update = GuestTrackSer.AddToString(model); string updateSql = string.Format("if not exists(select id from TB_GuestTrack where guestId='{0}' and yearNo='{1}' and QuartNo='{2}')begin {3} end else begin {4} end", model.GuestId, nextYear.ToString(), nextZhangQi.ToString(), update, secondUpdate); objCommand.CommandText = updateSql; objCommand.ExecuteNonQuery(); } tan.Commit(); ServiceAppSetting.LoggerHander.Invoke(string.Format("执行成功! - {0}", DateTime.Now), ""); } catch (Exception) { tan.Rollback(); ServiceAppSetting.LoggerHander.Invoke(string.Format("账期执行失败! - {0}", DateTime.Now), "Error"); } } } ////增加金蝶发票处理工具 //var kISModel = new KISService().GetKIS(); //var invoiceDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + kISModel.InvoiceDate); //var payableDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + kISModel.PayableDate); //if (DateTime.Now.Hour == invoiceDate.Hour && DateTime.Now.Minute == invoiceDate.Minute) //{ // var invoiceJob = new Thread(delegate () // { // try // { // ServiceAppSetting.LoggerHander.Invoke(string.Format("Invoice_Proc-开始! - {0}", DateTime.Now), ""); // var kisServer = new KISService(); // var invoicesList = kisServer.GetInvoiceErrorInfo(kISModel.AccountName); // if (invoicesList.Count > 0) // { // ServiceAppSetting.LoggerHander.Invoke(string.Format("Invoice_Proc-存在重复发票号数据,JOB 停止!", DateTime.Now), ""); // return; // } // //获取选中的数据库连接 // string newConn = KISService.KISDBConn.Replace("AcctCtl", kISModel.AccountName); // KISService.ExeCommand("Invoice_Proc", newConn, Convert.ToDateTime(kISModel.InvoiceFrom), Convert.ToDateTime(kISModel.InvoiceTo)); // ServiceAppSetting.LoggerHander.Invoke(string.Format("Invoice_Proc-结束! - {0}", DateTime.Now), ""); // } // catch (Exception ex) // { // ServiceAppSetting.LoggerHander.Invoke(string.Format("Invoice_Proc-结束! - {0},执行异常,请检查!错误信息:{1}", DateTime.Now, ex.Message), ""); // } // }) // { IsBackground = true }; // invoiceJob.Start(); //} //if (DateTime.Now.Hour == payableDate.Hour && DateTime.Now.Minute == payableDate.Minute) //{ // var payableJob = new Thread(delegate () // { // try // { // var kisServer = new KISService(); // var payableList = kisServer.GetPayaleErrorInfo(kISModel.AccountName); // if (payableList.Count > 0) // { // ServiceAppSetting.LoggerHander.Invoke(string.Format("Payable_Proc-存在重复发票号数据,JOB 停止!", DateTime.Now), ""); // return; // } // ServiceAppSetting.LoggerHander.Invoke(string.Format("Payable_Proc-开始! - {0}", DateTime.Now), ""); // //获取选中的数据库连接 // string newConn = KISService.KISDBConn.Replace("AcctCtl", kISModel.AccountName); // KISService.ExeCommand("Payable_Proc", newConn, Convert.ToDateTime(kISModel.PayableFrom), Convert.ToDateTime(kISModel.PayableTo)); // ServiceAppSetting.LoggerHander.Invoke(string.Format("Payable_Proc-结束! - {0}", DateTime.Now), ""); // } // catch (Exception ex) // { // ServiceAppSetting.LoggerHander.Invoke(string.Format("Payable_Proc-结束! - {0},执行异常,请检查!错误信息:{1}", DateTime.Now, ex.Message), ""); // } // }) // { IsBackground = true }; // payableJob.Start(); //} }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DateTime fromDate = Convert.ToDateTime("2000-1-1"); DateTime toDate = Convert.ToDateTime("2036-1-1"); using (SqlConnection conn = DBHelp.getConn()) { conn.Open(); var selectInfo = string.Format(@"select GoodId from TB_Good WHERE GoodStatus<>'不通过' and goodNo='12218'"); SqlCommand objCommand = conn.CreateCommand(); objCommand.CommandText = selectInfo; List <int> goodIdList = new List <int>(); using (var reader = objCommand.ExecuteReader()) { while (reader.Read()) { goodIdList.Add(Convert.ToInt32(reader["GoodId"])); } reader.Close(); } foreach (var goodId in goodIdList) { //查找进销存的所有信息 List <Pro_JSXDetailInfo> pOOrderList = this.jxcDetailSer.GetListArray(1, goodId, fromDate, toDate); pOOrderList.Sort(delegate(Pro_JSXDetailInfo a, Pro_JSXDetailInfo b) { return(a.RuTime.CompareTo(b.RuTime)); }); jxcDetailSer.ReSetPro_JSXDetailInfo(pOOrderList); decimal iniNum = 0; for (int i = 0; i < pOOrderList.Count; i++) { var model = pOOrderList[i]; if (i == 0) { iniNum = model.GoodInNum - model.GoodOutNum; //i++; } else { iniNum = iniNum + model.GoodInNum - model.GoodOutNum; } pOOrderList[i].GoodResultNum = iniNum; } //计算 Dictionary <int, decimal> inUpdatePrice = new Dictionary <int, decimal>(); Dictionary <int, decimal> outUpdatePrice = new Dictionary <int, decimal>(); Dictionary <int, decimal> sell_inUpdatePrice = new Dictionary <int, decimal>(); Dictionary <int, decimal> sell_outUpdatePrice = new Dictionary <int, decimal>(); Decimal avgPrice = 0; int startIndex = 0; foreach (var jxc in pOOrderList) { if (jxc.GoodResultNum != 0) { if (jxc.ProNo == "20120096") { } decimal topNum = startIndex > 1 ? pOOrderList[startIndex - 1].GoodResultNum : 0; if (jxc.TypeName == "采购入库") { avgPrice = (jxc.GoodInNum * jxc.avgHousePrice + avgPrice * topNum) / jxc.GoodResultNum; inUpdatePrice.Add(jxc.Ids, avgPrice); } if (jxc.TypeName == "销售退货") { avgPrice = (jxc.GoodInNum * jxc.avgHousePrice + avgPrice * topNum) / jxc.GoodResultNum; sell_outUpdatePrice.Add(jxc.Ids, avgPrice); } if (jxc.TypeName == "采购退货") { avgPrice = (avgPrice * topNum - jxc.GoodOutNum * jxc.avgHousePrice) / jxc.GoodResultNum; outUpdatePrice.Add(jxc.Ids, avgPrice); } if (jxc.TypeName == "销售出库") { avgPrice = (avgPrice * topNum - jxc.GoodOutNum * jxc.avgHousePrice) / jxc.GoodResultNum; sell_inUpdatePrice.Add(jxc.Ids, avgPrice); } } else { avgPrice = 0; } startIndex++; } StringBuilder sbSql = new StringBuilder(); foreach (var m in inUpdatePrice) { sbSql.AppendFormat("update CAI_OrderInHouses set TempHousePrice={1} where Ids={0}; ", m.Key, m.Value); } foreach (var m in outUpdatePrice) { sbSql.AppendFormat("update CAI_OrderOutHouses set TempHousePrice={1} where Ids={0}; ", m.Key, m.Value); } foreach (var m in sell_inUpdatePrice) { sbSql.AppendFormat("update Sell_OrderOutHouses set TempHousePrice={1} where Ids={0}; ", m.Key, m.Value); } foreach (var m in sell_outUpdatePrice) { sbSql.AppendFormat("update Sell_OrderInHouses set TempHousePrice={1} where Ids={0}; ", m.Key, m.Value); } if (!string.IsNullOrEmpty(sbSql.ToString())) { objCommand.CommandText = sbSql.ToString(); objCommand.ExecuteNonQuery(); } } } } }