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