笔记20150522-生成更新重复单据编号的update语句

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