public string GenereteUpdateSql(int type = 0,int localdb=0) { StringBuilder sb = new StringBuilder(); StringBuilder sbToday = new StringBuilder(); sbToday.Append("<font color='red'>"); if (localdb == 1) DbHelper.Conn = "Data Source=192.168.9.222;Initial Catalog=test;Persist Security Info=True;User ID=dev;Password=dev;"; else DbHelper.Conn = "Data Source=****;Initial Catalog=test;Persist Security Info=True;User ID=test;Password=test"; DbHelper db = new DbHelper(); string sql = "SELECT MAX(ReceiptNo) ReceiptNo FROM dbo.FinanceReceipts f GROUP BY f.ReceiptNo HAVING COUNT(f.ReceiptNo)>1";//WHERE SUBSTRING(f.ReceiptNo,3,2)!='LS' ,,COUNT(ReceiptNo) repeatcount,MAX(ObjectType) ObjectType,MAX(CreatedDate) CreatedDate,MAX(CASE WHEN IsSummary=1 THEN 1 ELSE 0 END ) isSummary, DataTable dtReceiptNo = db.ExecuteDataTable(sql); Dictionary<string, int> DateTypeSeriaNo = new Dictionary<string, int>(); if (dtReceiptNo != null && dtReceiptNo.Rows.Count > 0) { foreach (DataRow drReceiptNo in dtReceiptNo.Rows) { string ReceiptNoQuery = drReceiptNo["ReceiptNo"].ToString(); DataTable dtData = db.ExecuteDataTable(string.Format("select ReceiptId,ReceiptNo,ObjectType,CreatedDate,CASE WHEN ReceiptStatus=0 THEN 0 ELSE 1 END IsCommit FROM dbo.FinanceReceipts WHERE ReceiptNo='{0}' ", ReceiptNoQuery)); if (dtData != null && dtData.Rows.Count > 1)//同一编号大于2个 { for (int i = 0; i < dtData.Rows.Count - 1; i++)//更新前n-1个 { DataRow drData = dtData.Rows[i]; int ReceiptId = Convert.ToInt32(drData["ReceiptId"]); int ObjectType = Convert.ToInt32(drData["ObjectType"]); string CreatedDate = drData["CreatedDate"].ToString(); int maxSerial = 0; int IsCommit = Convert.ToInt32(drData["IsCommit"]); string DateTypeKey = string.Format("{0}{1}{2}", CreatedDate, ObjectType, IsCommit); if (DateTypeSeriaNo.ContainsKey(DateTypeKey)) { maxSerial = DateTypeSeriaNo[DateTypeKey]; } { string maxSerialSql = ""; if (IsCommit == 0) maxSerialSql = string.Format(" SELECT MAX(SerialNumber) FROM dbo.FinanceReceipts WHERE ObjectType={0} AND ReceiptStatus=0 AND CreatedDate='{1}' ", ObjectType, CreatedDate); else maxSerialSql = string.Format(" SELECT MAX(SerialNumber) FROM dbo.FinanceReceipts WHERE ObjectType={0} AND ReceiptStatus!=0 AND CreatedDate='{1}' ", ObjectType, CreatedDate); object retobj = db.ExecuteScalar(maxSerialSql); int dbMaxSerail = 0; if (retobj != DBNull.Value) dbMaxSerail = Convert.ToInt32(retobj); maxSerial=(maxSerial>dbMaxSerail?maxSerial:dbMaxSerail); DateTypeSeriaNo[DateTypeKey] = maxSerial;//存入dict } string ReceiptNo = string.Empty; switch (ObjectType) { case 1: // 应收 ReceiptNo = "YS"; break; case 2: // 收款 ReceiptNo = "SK"; break; case 3: // 应付 ReceiptNo = "YF"; break; case 4: // 付款 ReceiptNo = "FK"; break; default: throw new InvalidOperationException("未知票据类型,不能生成单据编号"); } // 未提交 if (IsCommit == 0 || ReceiptNoQuery.Substring(2, 2) == "LS") { ReceiptNo += "LS"; } ReceiptNo += CreatedDate; maxSerial++; DateTypeSeriaNo[DateTypeKey] = maxSerial; ReceiptNo += string.Format("{0:D6}", maxSerial); string CreatedDateToday=DateTime.Now.ToString("yyyyMMdd"); if (CreatedDate == CreatedDateToday) { sbToday.AppendFormat("UPDATE dbo.FinanceReceipts SET SerialNumber={0},ReceiptNo='{1}' WHERE ReceiptId={2};<br/>", maxSerial, ReceiptNo, ReceiptId); sbToday.AppendFormat("INSERT INTO dbo.FinanceBillLog(ReceiptId,[Action] ,Remark ,CreatedById ,CreatedByName ,CreatedDate) VALUES ({0},N'修改重复付款单编号' ,N'从 {1} 改为 {2}' ,0 ,N'sql' ,'2015-05-22 15:30:00');<br/>", ReceiptId, ReceiptNoQuery, ReceiptNo); } else { sb.AppendFormat("UPDATE dbo.FinanceReceipts SET SerialNumber={0},ReceiptNo='{1}' WHERE ReceiptId={2};<br/>", maxSerial, ReceiptNo, ReceiptId); sb.AppendFormat("INSERT INTO dbo.FinanceBillLog(ReceiptId,[Action] ,Remark ,CreatedById ,CreatedByName ,CreatedDate) VALUES ({0},N'修改重复付款单编号' ,N'从 {1} 改为 {2}' ,0 ,N'sql' ,'2015-05-22 15:30:00');<br/>", ReceiptId, ReceiptNoQuery, ReceiptNo); } } } } } sbToday.Append("</font>"); if (type == 1) return sb.ToString(); if (type == 2) return sbToday.ToString(); return sb.ToString() + "<br/><br/><br/>" + sbToday.ToString(); }
时间: 2024-10-20 02:00:20