protected IDataReader Get_Additional_Items() { IDataReader idr = null; if (Request.QueryString["gid"] == null) { iDB.Communicate dbComm = new iDB.Communicate(); string query = string.Format("select *, \"Amount\" = Case When addtionalTransAmountDependent = 1 Then Cast(additionalTransAmount as varchar) + '%' Else Cast(additionalTransAmount as varchar) End From fin_ModuleItems{0}", string.Format(" where moduleId={0}", moduleId)); query += string.Format(" and addtionalTransIsOfficial={0}", isOfficial); query += " and (moduleIParameterId=0 or moduleIParameterId is null)"; //if (isOfficial == 1) //{ // query += " and additionalTransIsOfficial=1"; //} idr = dbComm.SelectCMD(query); } return(idr); }
protected void Load_Related_Data(int groupID) { bool hasTrans = false; IDataReader idr = null; iDB.Communicate dbComm = new iDB.Communicate(); string query = string.Format("select *, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo from vw_page_transactions where transLinkedToGroup={0}", groupID); idr = dbComm.SelectCMD(query); if (idr != null) { while (idr.Read()) { hasTrans = true; } } idr = dbComm.SelectCMD(query); lvGridRelated.DataSource = idr; lvGridRelated.DataBind(); rdPanelRelated.Visible = hasTrans; }
private void Load_Data() { IDataReader idr = new iDB.Communicate().SelectCMD("select * from fin_Taxes"); lvGrid.DataSource = idr; lvGrid.DataBind(); }
public IDataReader Select_Branches() { iDB.Communicate dbComm = new iDB.Communicate(); IDataReader idr = dbComm.SelectCMD("select * from fin_Branches"); return(idr); }
protected void Load_Entities() { iDB.Communicate dbComm = new iDB.Communicate(); IDataReader idr = dbComm.SelectCMD("select * from vw_Entities"); lvGrid.DataSource = idr; lvGrid.DataBind(); }
protected string Imbalanced_Vouchars() { iDB.Communicate dbComm = new iDB.Communicate(); iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups(); iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup(); iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact(); iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); IDataReader idr = dbComm.SelectCMD("Select transGroupID, Sum(transAmount) as transAmount, max(transNarration) as transNarration, max(transCreatedOn) as transCreatedOn from fin_Transactions where transGroupID in (select transGroupID from fin_TransGroups) group by transGroupID"); string output = ""; if (idr != null) { while (idr.Read()) { int transGroupID = Convert.ToInt32(idr["transGroupID"]); objTransG = new iBiz.FinPro.Transactions.Groups.objGroup(); objTransG = bTransG.Select(transGroupID); decimal crTotal = 0, drTotal = 0, OCrTotal = 0, ODrTotal = 0; if (objTransG != null) { if (objTransG.Get_Transactions() != null) { foreach (var item in objTransG.Get_Transactions()) { if (item.transDrAccount == null) { OCrTotal += item.transAmount; } else { ODrTotal += item.transAmount; } } } if (ODrTotal == OCrTotal) { //output += string.Format("<td>Balanced<td>"); } else { if (!string.IsNullOrEmpty(output)) { output += ","; } output += transGroupID.ToString(); } } } } return(output); }
public IDataReader Get_Preselected_Items(int moduleId, int moduleIParameterId, bool isOfficial) { iDB.Communicate dbComm = new iDB.Communicate(); IDataReader idr = null; string query = string.Format("exec SP_fin_Preselected_Vouchar_Accounts @moduleId={0}, @moduleParameterId={1}, @moduleIsOfficial={2}", moduleId, moduleIParameterId, isOfficial); idr = dbComm.SelectCMD(query); return(idr); }
protected void Payments() { iDB.Communicate dbComm = new iDB.Communicate(); iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups(); iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup(); iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact(); iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); IDataReader idr = dbComm.SelectCMD("Select transGroupID, Sum(transAmount) as transAmount, max(transNarration) as transNarration, max(transCreatedOn) as transCreatedOn from fin_Transactions where transType=3 group by transGroupID"); if (idr != null) { while (idr.Read()) { int transGroupId = Convert.ToInt32(idr["transGroupId"]); objTransG.transGroupCreatedBy = 1; objTransG.transGroupCreatedOn = Convert.ToDateTime(idr["transCreatedOn"]); objTransG.transGroupForeNumber = bTransG.New_Group_Fore_Number(2, "INV", DateTime.Now); objTransG.transGroupID = transGroupId; objTransG.transGroupPrefixNo = 2; objTransG.transGroupPrefixString = "INV"; objTransG.transGroupStatus = 1; objTransG.transGroupTitle = "Invoice Created on " + objTransG.transGroupCreatedOn.ToString("MMM dd, yyyy"); objTransG.transGroupTotalAmount = Convert.ToDecimal(idr["transAmount"]); objTransG.transTransCount = 0; bTransG.Add(objTransG); } } idr = null; idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where (transType=3) and Not (transDrAccount is Null or transCrAccount is Null)"); if (idr != null) { while (idr.Read()) { int transId = Convert.ToInt32(idr["transId"]); iBiz.FinPro.Transactions.Transact.objTransaction objTempTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans = bTrans.Select(transId); objTempTrans = bTrans.Select(transId); objTrans.transCrAccount = null; //objTrans.transType = 2; objTempTrans.transDrAccount = null; //objTempTrans.transType = 2; bTrans.Update(objTrans); bTrans.Add(objTempTrans); } } }
protected void Fix_Inv() { iDB.Communicate dbComm = new iDB.Communicate(); IDataReader idr = dbComm.SelectCMD("select transGroupID, sum(transAmount) as transAmount, max(transCreatedOn) as transCreatedOn from fin_transactions where (transDrAccount=84 or transCRAccount=84) and transAmount>0 and transSystemIndex=2 and Not transGroupID in (select transGroupID from fin_transactions where transDrAccount=84 and transType=44) group by transGroupID"); iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact(); if (idr != null) { while (idr.Read()) { int transGroupID = Convert.ToInt32(idr["transGroupID"]); decimal transTotalAmount = Convert.ToDecimal(idr["transAmount"]); DateTime transCreatedOn = Convert.ToDateTime(idr["transCreatedOn"]); iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans.transSystemIndex = 2; objTrans.transParticipantID = 8; objTrans.transDrAccount = 84; objTrans.transNarration = "Inventory Sold from DHA Shop"; objTrans.transAmount = transTotalAmount; objTrans.transCreatedBy = 1; objTrans.transCreatedOn = transCreatedOn; objTrans.transUpdatedBy = 1; objTrans.transUpdatedOn = DateTime.Now; objTrans.transStatus = 1; objTrans.transSystemRestrict = false; objTrans.transGroupID = transGroupID; objTrans.transIsCompound = true; objTrans.transType = 44; bTrans.Add(objTrans); objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans.transSystemIndex = 2; objTrans.transParticipantID = 8; objTrans.transCrAccount = 16; objTrans.transNarration = "Inventory Sold from DHA Shop"; objTrans.transAmount = transTotalAmount; objTrans.transCreatedBy = 1; objTrans.transCreatedOn = transCreatedOn; objTrans.transUpdatedBy = 1; objTrans.transUpdatedOn = DateTime.Now; objTrans.transStatus = 1; objTrans.transSystemRestrict = false; objTrans.transGroupID = transGroupID; objTrans.transIsCompound = true; objTrans.transType = 44; bTrans.Add(objTrans); } } }
public IDataReader Select_By_Type(int AccountCategoryID, int accountParent) { IDataReader idr = null; iDB.Communicate dbComm = new iDB.Communicate(); string query = "select * from fin_Accounts where accountParent=" + accountParent; query += " and accountType=" + AccountCategoryID.ToString(); idr = dbComm.SelectCMD(query); return(idr); }
protected IDataReader Get_Grid_Data() { int maxRows = 0; iDB.Communicate dbComm = new iDB.Communicate(); IDataReader idr = null; string query = ""; query = string.Format("select *, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo, ROW_NUMBER() Over (Order by transGroupCreatedOn desc) as RowNo from vw_page_transactions where transGroupId in ({0})", Imbalanced_Vouchars()); idr = dbComm.SelectCMD(query); return(idr); }
public IDataReader Select(bool isOfficial) { IDataReader idr = null; iDB.Communicate dbComm = new iDB.Communicate(); string query = "select * from fin_Accounts where accountSystemIndex in (0, 1) order by accountTitle asc"; if (isOfficial) { query = "select * from fin_Accounts where accountSystemIndex in (1, 2) order by accountTitle asc"; } idr = dbComm.SelectCMD(query); return(idr); }
public IDataReader Select_By_Type(int AccountCategoryID, bool isOfficial) { IDataReader idr = null; iDB.Communicate dbComm = new iDB.Communicate(); string query = "select * from fin_Accounts where accountSystemIndex in (0, 1)"; if (isOfficial) { query = "select * from fin_Accounts where accountSystemIndex in (1, 2)"; } query += " and accountType=" + AccountCategoryID.ToString(); idr = dbComm.SelectCMD(query); return(idr); }
public int GetAccountId(string accountTitle) { int accountId = 0; IDataReader idr = null; iDB.Communicate dbComm = new iDB.Communicate(); string query = "select * from fin_Accounts where accountTitle='" + accountTitle.Trim() + "'"; idr = dbComm.SelectCMD(query); if (idr != null) { while (idr.Read()) { accountId = Convert.ToInt32(idr["accountId"]); } } return(accountId); }
protected IDataReader Get_Grid_Data() { iDB.Communicate dbComm = new iDB.Communicate(); IDataReader idr = null; string query = ""; query = string.Format("select *, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo, ROW_NUMBER() Over (Order by transGroupCreatedOn desc) as RowNo from vw_page_transactions where transGroupPrefixNo={0}", moduleId); if (isOfficial == 1) { query = string.Format("select *, IsNull(transGroupOfficialTotalAmount, 0) as groupTotal, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo, ROW_NUMBER() Over (Order by transGroupCreatedOn desc) as RowNo from vw_page_transactions where not transGroupStatus=3 and transGroupPrefixNo={0}", moduleId); //query += " and transGroupIsOfficial=1"; // Official Condition DateTime?nullDate = null; tbFromDate_CalendarExtender.SelectedDate = tbFromDate.Text.Trim().Length > 0 ? Convert.ToDateTime(tbFromDate.Text) : nullDate; tbToDate_CalendarExtender.SelectedDate = tbToDate.Text.Trim().Length > 0 ? Convert.ToDateTime(tbToDate.Text) : nullDate; query += tbFromDate_CalendarExtender.SelectedDate.HasValue && !tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and transGroupCreatedOn>='{0}'", Convert.ToDateTime(tbFromDate.Text)) : ""; query += !tbFromDate_CalendarExtender.SelectedDate.HasValue && tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and transGroupCreatedOn<='{0}'", Convert.ToDateTime(tbToDate.Text)) : ""; query += !tbFromDate_CalendarExtender.SelectedDate.HasValue && tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and (transGroupCreatedOn between '{0}' and '{1}')", Convert.ToDateTime(tbFromDate.Text), Convert.ToDateTime(tbToDate.Text)) : ""; query += tbDescription.Text.Trim().Length >= 0 ? string.Format(" and transGroupTitle like '%{0}%'", tbDescription.Text.Trim()) : ""; } else { query = string.Format("select *, transGroupTotalAmount as groupTotal, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo, ROW_NUMBER() Over (Order by transGroupCreatedOn desc) as RowNo from vw_page_transactions where not transGroupStatus=3 and transGroupPrefixNo={0}", moduleId); DateTime?nullDate = null; tbFromDate_CalendarExtender.SelectedDate = tbFromDate.Text.Trim().Length > 0 ? Convert.ToDateTime(tbFromDate.Text) : nullDate; tbToDate_CalendarExtender.SelectedDate = tbToDate.Text.Trim().Length > 0 ? Convert.ToDateTime(tbToDate.Text) : nullDate; query += tbFromDate_CalendarExtender.SelectedDate.HasValue && !tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and transGroupCreatedOn>='{0}'", Convert.ToDateTime(tbFromDate.Text)) : ""; query += !tbFromDate_CalendarExtender.SelectedDate.HasValue && tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and transGroupCreatedOn<='{0}'", Convert.ToDateTime(tbToDate.Text)) : ""; query += !tbFromDate_CalendarExtender.SelectedDate.HasValue && tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and (transGroupCreatedOn between '{0}' and '{1}')", Convert.ToDateTime(tbFromDate.Text), Convert.ToDateTime(tbToDate.Text)) : ""; query += tbDescription.Text.Trim().Length >= 0 ? string.Format(" and transGroupTitle like '%{0}%'", tbDescription.Text.Trim()) : ""; } idr = dbComm.SelectCMD("select IsNull(max(RowNo), 0) as maxRows from (" + query + ") as Results"); if (idr != null) { while (idr.Read()) { maxRows = Convert.ToInt32(idr["maxRows"]); } } int recordNoFrom = (pageSize * currentPage) - pageSize; int recordNoTo = recordNoFrom + pageSize; query = string.Format("select * from (" + query + ") as results where RowNo>={0} and RowNo<{1}", recordNoFrom, recordNoTo); idr = dbComm.SelectCMD(query); Do_Pagination(null, null); return(idr); }
protected void btnCreate_Click(object sender, EventArgs e) { iDB.Communicate dbComm = new iDB.Communicate(); DataTable dt = new DataTable(); IDataReader idr = null; SortedList <string, object> srt = new SortedList <string, object>(); string moduleName = ".FinPro"; string bizModuleName = ".FinPro"; string bizPath = @"D:\Projects\FinPro\FinPro\iBiz\FinPro\Modules\" + tbClassFileName.Text + ".cs"; string idbPath = @"D:\Projects\FinPro\FinPro\iDB\FinPro\Modules\" + tbClassFileName.Text + ".cs"; //dt = dbComm.SelectExecute(iDB.Payroll.Communicate.StoredProcedures.coder, srt); //idr = dbComm.SelectCMD("sp_"); using (SqlConnection Cn = new SqlConnection("Data Source=localhost;Initial Catalog=FinApp; User Id=sa; Password=Aa1Bb2!@")) { SqlCommand Cm = new System.Data.SqlClient.SqlCommand(); SqlDataAdapter Adp = new SqlDataAdapter(); DataSet ds = new DataSet(); Cm.Connection = Cn; Cm.CommandText = string.Format("sp_help '{0}'", tbname.Text); Cm.Connection.Open(); //Adp.FillSchema(ds, SchemaType.Source); //Cm.ExecuteNonQuery(); //idr = Cm.ExecuteReader(); Adp.SelectCommand = Cm; Adp.Fill(ds); dt = ds.Tables[1]; //return idr; } if (dt != null) { // Create Procedure string proc = "Create Procedure SP_" + tbprocname.Text + "(<br />"; string structProc = string.Empty; string insertVar = string.Empty; string updateVar = string.Empty; string primaryCol = string.Empty; for (int i = 0; i < dt.Rows.Count; i++) { string colName = dt.Rows[i]["Column_name"].ToString(); string colType = dt.Rows[i]["Type"].ToString(); string colLength = dt.Rows[i]["Length"].ToString(); string colNullable = dt.Rows[i]["Nullable"].ToString(); string colPrec = dt.Rows[i]["Prec"].ToString(); string colScale = dt.Rows[i]["Scale"].ToString(); string colLengthAdd = ""; if (colType.ToLower() == "decimal") { colLengthAdd = "(" + colPrec + ", " + colScale + ")"; } else if (colType.ToLower() == "varchar" || colType.ToLower() == "nvarchar") { colLengthAdd = "(" + colLength + ")"; } colType += " " + colLengthAdd; colName = "@" + colName; if (i != 0) { if (i != dt.Rows.Count - 1) { insertVar += colName + ","; updateVar += colName.Replace("@", "") + "=" + colName + ","; } else { insertVar += colName; updateVar += colName.Replace("@", "") + "=" + colName; } } else { primaryCol = colName; } structProc += colName + " " + colType + "=null,<br />"; } structProc += "@Flg int"; string Creater = "Create Procedure " + tbprocname.Text + "(<br />"; string closeCreater = ") as Begin <br />"; dt.TableName = tbname.Text; string insertedID = String.Format("Declare @lastID int<br />Set @lastID=IsNull((select max({0}) from {1}), 0)<br />Set {2}=@lastID+1<br />", primaryCol.Replace("@", ""), dt.TableName, primaryCol); string flg1 = String.Format("if @Flg=1<br />Begin<br />{1}<br />insert into " + dt.TableName + " output inserted.{0} values(" + insertVar + ") <br />End<br />", primaryCol.Replace("@", ""), insertedID); string flg2 = "if @Flg=2<br />Begin<br />update " + dt.TableName + " set " + updateVar + " where " + primaryCol.Replace("@", "") + "=" + primaryCol + " <br />End<br />"; string flg3 = "if @Flg=3<br />Begin<br />delete from " + dt.TableName + " where " + primaryCol.Replace("@", "") + "=" + primaryCol + " <br />End<br />"; string flg4 = "if @Flg=4<br />Begin<br /> select * from " + dt.TableName + " <br />End<br />"; string flg5 = "if @Flg=5<br />Begin<br /> select * from " + dt.TableName + " where " + primaryCol.Replace("@", "") + "=" + primaryCol + "<br />End<br />"; string hugeSeprator = "<br /><br />"; string dbCreater = Creater + structProc + closeCreater + flg1 + flg2 + flg3 + flg4 + flg5 + "<br /> End" + hugeSeprator; //dbComm.Execute(dbCreater.Replace("<br />", " ")); //Create DB Class string dbCommClass = "public class " + tbname.Text + "<br />{<br />Communicate dbComm = new Communicate();<br />SortedList" + Server.HtmlEncode("<string, object>") + " srt = new " + Server.HtmlEncode("SortedList<string, object>()") + ";<br />"; string dbAdd = "public void Add("; string dbUpdate = "public void Update("; string dbDelete = "public void Delete("; string dbSelect = "public IDataReader Select()<br />"; string dbSelectThis = "public IDataReader Select("; string dbAllAttribs = ""; string firstAttrib = ""; for (int i = 0; i < dt.Rows.Count; i++) { string colName = dt.Rows[i]["Column_name"].ToString(); string colType = dt.Rows[i]["Type"].ToString(); string colLength = dt.Rows[i]["Length"].ToString(); string colNullable = dt.Rows[i]["Nullable"].ToString(); string colPrec = dt.Rows[i]["Prec"].ToString(); string colScale = dt.Rows[i]["Scale"].ToString(); string colTypeCSharpe = ""; if (colType.ToLower().Contains("varchar")) { colTypeCSharpe = "string"; } else if (colType.ToLower() == "int" || colType.ToLower() == "tinyint") { colTypeCSharpe = "int"; } else if (colType.ToLower() == "decimal" || colType.ToLower() == "money") { colTypeCSharpe = "decimal"; } else if (colType.ToLower() == "datetime") { colTypeCSharpe = "DateTime"; } else if (colType.ToLower() == "bit") { colTypeCSharpe = "bool"; } if (colNullable.ToLower() == "yes" && colTypeCSharpe != "string") { colTypeCSharpe += "? "; } else { colTypeCSharpe += " "; } dbAllAttribs += "srt.Add(\"" + colName + "\", " + colName + ");<br />"; if (i == 0) { dbAdd += colTypeCSharpe + colName; dbUpdate += colTypeCSharpe + colName; dbDelete += colTypeCSharpe + colName; dbSelectThis += colTypeCSharpe + colName; firstAttrib += "srt.Add(\"" + colName + "\", " + colName + ");<br />"; } else { dbAdd += ", " + colTypeCSharpe + colName; dbUpdate += ", " + colTypeCSharpe + colName; } } dbAdd += "){<br />srt.Clear();<br />" + dbAllAttribs + "<br />srt.Add(\"Flg\", 1);<br />dbComm.Execute(srt, Communicate.StoredProcedures." + tbproc.Text + ");<br />}<br />"; dbUpdate += "){<br />srt.Clear();<br />" + dbAllAttribs + "<br />srt.Add(\"Flg\", 2);<br />dbComm.Execute(srt, Communicate.StoredProcedures." + tbproc.Text + ");<br />}<br />"; dbDelete += "){<br />srt.Clear();<br />" + firstAttrib + "<br />srt.Add(\"Flg\", 3);<br />dbComm.Execute(srt, Communicate.StoredProcedures." + tbproc.Text + ");<br />}<br />"; dbSelect += "{<br />srt.Clear();<br />IDataReader idr = null;<br />" + "srt.Add(\"Flg\", 4);<br />idr = dbComm.SelectIDR(Communicate.StoredProcedures." + tbproc.Text + ", srt);<br />return idr;<br />}<br />"; dbSelectThis += "){<br />srt.Clear();<br />IDataReader idr = null;<br />" + firstAttrib + "<br />srt.Add(\"Flg\", 5);<br />idr = dbComm.SelectIDR(Communicate.StoredProcedures." + tbproc.Text + ", srt);<br />return idr;<br />}<br />"; dbCommClass += dbAdd + dbUpdate + dbDelete + dbSelect + dbSelectThis; dbCommClass += "<br />}<br />"; // Create Business Object string bizObject = "public class obj" + tbname.Text + "<br />{<br />"; for (int i = 0; i < dt.Rows.Count; i++) { string colName = dt.Rows[i]["Column_name"].ToString(); string colType = dt.Rows[i]["Type"].ToString(); string colLength = dt.Rows[i]["Length"].ToString(); string colNullable = dt.Rows[i]["Nullable"].ToString(); string colPrec = dt.Rows[i]["Prec"].ToString(); string colScale = dt.Rows[i]["Scale"].ToString(); string colTypeCSharpe = ""; if (colType.ToLower().Contains("varchar")) { colTypeCSharpe = "string"; } else if (colType.ToLower() == "int" || colType.ToLower() == "tinyint") { colTypeCSharpe = "int"; } else if (colType.ToLower() == "decimal" || colType.ToLower() == "money") { colTypeCSharpe = "decimal"; } else if (colType.ToLower() == "datetime") { colTypeCSharpe = "DateTime"; } else if (colType.ToLower() == "bit") { colTypeCSharpe = "bool"; } if (colNullable.ToLower() == "yes" && colTypeCSharpe != "string") { colTypeCSharpe += "? "; } else { colTypeCSharpe += " "; } bizObject += "public " + colTypeCSharpe + colName + " { get; set; }<br />"; } bizObject += "<br />}<br />"; //Create Business Class string bizClass = "public class " + tbname.Text + "<br />{<br />iDB" + moduleName + "." + tbname.Text + " db = new iDB" + moduleName + "." + tbname.Text + "();<br />"; string addRoutine = "public void Add(obj" + tbname.Text + " i)<br />{<br />db.Add("; string updateRoutine = "public void Update(obj" + tbname.Text + " i)<br />{<br />db.Update("; string deleteRoutine = "public void Delete(int **0**)<br />{<br />db.Delete(**0**);<br />}<br />"; string selectRoutine = "public IDataReader Select()<br />{<br />return db.Select();<br />}<br />"; string selectThisRoutine = "public obj" + tbname.Text + " Select(int **0**)<br />{<br />IDataReader idr = db.Select(**0**);<br />return Select_Obj(idr);<br />}<br />"; string selectObjPrivate = "private obj" + tbname.Text + " Select_Obj(IDataReader idr)<br />{<br />obj" + tbname.Text + " o = new obj" + tbname.Text + "();<br />bool rtNull = true;<br />if (idr != null){<br />while (idr.Read())<br />{<br />rtNull = false;<br />"; for (int i = 0; i < dt.Rows.Count; i++) { string colName = dt.Rows[i]["Column_name"].ToString(); string colType = dt.Rows[i]["Type"].ToString(); string colLength = dt.Rows[i]["Length"].ToString(); string colNullable = dt.Rows[i]["Nullable"].ToString(); string colPrec = dt.Rows[i]["Prec"].ToString(); string colScale = dt.Rows[i]["Scale"].ToString(); string colTypeCSharpe = ""; if (colType.ToLower().Contains("varchar")) { colTypeCSharpe = "string"; } else if (colType.ToLower() == "int" || colType.ToLower() == "tinyint") { colTypeCSharpe = "int32"; } else if (colType.ToLower() == "decimal" || colType.ToLower() == "money") { colTypeCSharpe = "decimal"; } else if (colType.ToLower() == "datetime") { colTypeCSharpe = "DateTime"; } else if (colType.ToLower() == "bit") { colTypeCSharpe = "bool"; } TextInfo tInfo = new CultureInfo("en-US", false).TextInfo; selectObjPrivate += "if (idr[\"" + colName + "\"] != DBNull.Value){<br />o." + colName + " = Convert.To" + tInfo.ToTitleCase(colTypeCSharpe).Replace("Bool", "Boolean").Replace("Datetime", "DateTime") + "(idr[\"" + colName + "\"]);<br />}<br />"; if (colNullable.ToLower() == "yes" && colTypeCSharpe != "string") { colTypeCSharpe += "? "; } else { colTypeCSharpe += " "; } if (i == 0) { addRoutine += "i." + colName; updateRoutine += "i." + colName; deleteRoutine = deleteRoutine.Replace("**0**", colName); selectThisRoutine = selectThisRoutine.Replace("**0**", colName); } else { addRoutine += ", i." + colName; updateRoutine += ", i." + colName; } } addRoutine += ");<br />}<br />"; updateRoutine += ");<br />}<br />"; deleteRoutine += "<br />"; selectThisRoutine += "<br />"; selectObjPrivate += "}<br />}<br /> if (rtNull){<br />o = null;<br />}<br />return o; <br />}<br />"; bizClass += addRoutine + updateRoutine + deleteRoutine + selectRoutine + selectThisRoutine + selectObjPrivate; bizClass += "<br /><br />" + bizObject + "<br />}<br />"; string importChunks = "using System;<br />using System.Collections.Generic;<br />using System.Linq;<br />using System.Web;<br />using System.Data;<br />"; bizClass = importChunks + "<br />namespace iBiz" + bizModuleName + "{<br />" + bizClass + "<br />}"; dbCommClass = importChunks + "<br />namespace iDB" + moduleName + "{<br />" + dbCommClass + "<br />}"; Edit_Communicate(tbproc.Text, tbprocname.Text); string[] bizFile = { Server.HtmlDecode(bizClass).Replace("<br />", "\r\n") }; System.IO.File.WriteAllLines(bizPath, bizFile); string[] dbFile = { Server.HtmlDecode(dbCommClass).Replace("<br />", "\r\n") }; System.IO.File.WriteAllLines(idbPath, dbFile); Response.Write(dbCreater + bizClass + dbCommClass); //Response.Write(dbCreater + dbAdd + hugeSeprator + dbUpdate + hugeSeprator + dbDelete + hugeSeprator + dbSelect + hugeSeprator + dbSelectThis + hugeSeprator + allObj + hugeSeprator + objDBBind + hugeSeprator + bizAdd + hugeSeprator + bizUpdate + hugeSeprator + bizDelete + hugeSeprator + bizSelectObj + hugeSeprator + allControls); } }
protected void Vouchar_Verify() { iDB.Communicate dbComm = new iDB.Communicate(); iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups(); iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup(); iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact(); iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); IDataReader idr = dbComm.SelectCMD("Select transGroupID, Sum(transAmount) as transAmount, max(transNarration) as transNarration, max(transCreatedOn) as transCreatedOn from fin_Transactions where transGroupID in (select transGroupID from fin_TransGroups) group by transGroupID"); string output = "<table>"; if (idr != null) { while (idr.Read()) { int transGroupID = Convert.ToInt32(idr["transGroupID"]); objTransG = new iBiz.FinPro.Transactions.Groups.objGroup(); objTransG = bTransG.Select(transGroupID); decimal crTotal = 0, drTotal = 0, OCrTotal = 0, ODrTotal = 0; output += "<tr><td>" + transGroupID.ToString() + "</td>"; if (objTransG != null) { if (objTransG.Get_Transactions(true) != null) { foreach (var item in objTransG.Get_Transactions(true)) { if (item.transDrAccount == null) { OCrTotal += item.transAmount; } else { ODrTotal += item.transAmount; } } } if (ODrTotal == OCrTotal) { output += string.Format("<td>Balanced<td>"); } else { output += string.Format("<td>Imbalanced<td>"); } if (objTransG.Get_Transactions(false) != null) { foreach (var item in objTransG.Get_Transactions(false)) { if (item.transDrAccount == null) { crTotal += item.transAmount; } else { drTotal += item.transAmount; } } } if (drTotal == crTotal) { output += string.Format("<td>Balanced<td>"); } else { output += string.Format("<td>Imbalanced<td>"); } } output += "</tr>"; } } output += "</table>"; Response.Write(output); }
protected void Balance_Fabric_For_Sales() { iDB.Communicate dbComm = new iDB.Communicate(); iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups(); iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup(); iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact(); iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); IDataReader idr = dbComm.SelectCMD("Select transGroupID from fin_Transactions where transCrAccount=16 order by transGroupID desc"); if (idr != null) { while (idr.Read()) { int transGroupId = Convert.ToInt32(idr["transGroupId"]); decimal officialTotal = 0; decimal unofficialTotal = 0; IDataReader idrr = dbComm.SelectCMD(string.Format("select IsNull(sum(transAmount), 0) from (select distinct(transAmount) from fin_Transactions where transGroupID={0} and not transCrAccount=16 and transSystemIndex=2) as resultBox", transGroupId)); if (idrr != null) { while (idrr.Read()) { officialTotal = Convert.ToDecimal(idrr[0]); } } idrr = dbComm.SelectCMD(string.Format("select IsNull(sum(transAmount), 0) from (select distinct(transAmount) from fin_Transactions where transGroupID={0} and not transCrAccount=16 and transSystemIndex=1) as resultBox", transGroupId)); if (idrr != null) { while (idrr.Read()) { unofficialTotal = Convert.ToDecimal(idrr[0]); unofficialTotal += officialTotal; } } idrr = dbComm.SelectCMD(string.Format("select * from fin_Transactions where transGroupID={0} and transCrAccount=16", transGroupId)); if (idrr != null) { while (idrr.Read()) { int transID = Convert.ToInt32(idrr["transID"]); objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans = bTrans.Select(transID); if (objTrans != null) { objTrans.transSystemIndex = 1; objTrans.transAmount = unofficialTotal; bTrans.Update(objTrans); } objTrans = bTrans.Select(transID); if (objTrans != null) { iBiz.FinPro.Transactions.Transact.objTransaction objTranss = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans.transSystemIndex = 2; objTrans.transAmount = officialTotal; objTrans.transSystemRestrict = true; objTranss.transParticipantID = objTrans.transParticipantID; objTranss.transCreatedOn = objTrans.transCreatedOn; bTrans.Add(objTrans); if (objTranss.transParticipantID.Value == 8) { objTranss.transDrAccount = 84; } else if (objTranss.transParticipantID.Value == 13) { objTranss.transDrAccount = 94; } else { objTranss.transDrAccount = null; } objTranss.transAmount = officialTotal; objTranss.transCrAccount = null; objTranss.transCreatedBy = 1; objTranss.transCreatedOn = DateTime.Now; objTranss.transGroupID = transGroupId; objTranss.transNarration = "Inventory Sold"; objTranss.transStatus = 1; objTranss.transSystemIndex = 2; objTranss.transSystemRestrict = true; objTranss.transType = 44; objTranss.transUpdatedBy = 1; objTranss.transUpdatedOn = DateTime.Now; bTrans.Add(objTranss); } } } objTransG = new iBiz.FinPro.Transactions.Groups.objGroup(); objTransG = bTransG.Select(transGroupId); if (objTransG != null) { objTransG.transGroupTotalAmount = unofficialTotal; objTransG.transGroupOfficialTotalAmount = officialTotal; bTransG.Update(objTransG); } } } }
protected void Import_Sales() { iDB.Communicate dbComm = new iDB.Communicate(); iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups(); iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup(); iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact(); iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); IDataReader idr = dbComm.SelectCMD("Select transGroupID, Sum(transAmount) as transAmount, max(transNarration) as transNarration, max(transCreatedOn) as transCreatedOn, max(transParticipantID) as transParticipantID from fin_Transactions where (transParticipantID=8 or transParticipantID=13) group by transGroupID"); if (idr != null) { while (idr.Read()) { int transGroupId = Convert.ToInt32(idr["transGroupId"]); string branch = ""; if (idr["transParticipantID"].ToString().Trim() == "8") { branch = "DHA - "; } else { branch = "Gujrat - "; } objTransG.transGroupCreatedBy = 1; objTransG.transGroupCreatedOn = Convert.ToDateTime(idr["transCreatedOn"]); objTransG.transGroupForeNumber = bTransG.New_Group_Fore_Number(4, "SAL", DateTime.Now); objTransG.transGroupID = transGroupId; objTransG.transGroupPrefixNo = 4; objTransG.transGroupPrefixString = "SAL"; objTransG.transGroupStatus = 1; objTransG.transGroupTitle = branch + "Sales Made on " + objTransG.transGroupCreatedOn.ToString("MMM dd, yyyy"); objTransG.transGroupTotalAmount = Convert.ToDecimal(idr["transAmount"]); objTransG.transTransCount = 0; bTransG.Add(objTransG); } } idr = null; idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where (transParticipantID=8 or transParticipantID=13) and Not (transDrAccount is Null or transCrAccount is Null)"); if (idr != null) { while (idr.Read()) { int transId = Convert.ToInt32(idr["transId"]); iBiz.FinPro.Transactions.Transact.objTransaction objTempTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans = bTrans.Select(transId); objTempTrans = bTrans.Select(transId); objTrans.transCrAccount = null; objTrans.transType = 44; objTempTrans.transDrAccount = null; objTempTrans.transType = 44; bTrans.Update(objTrans); bTrans.Add(objTempTrans); } } idr = null; idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where (transParticipantID=8 or transParticipantID=13)"); if (idr != null) { while (idr.Read()) { int transId = Convert.ToInt32(idr["transId"]); iBiz.FinPro.Transactions.Transact.objTransaction objTempTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans = bTrans.Select(transId); objTrans.transType = 44; bTrans.Update(objTrans); } } }
protected void Page_Load(object sender, EventArgs e) { iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact(); iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); iBiz.FinPro.Transactions.Transact.objTransaction objDrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); iBiz.FinPro.Transactions.Transact.objTransaction objCrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); iBiz.FinPro.Transactions.Groups bTGroup = new iBiz.FinPro.Transactions.Groups(); iBiz.FinPro.Transactions.Groups.objGroup objTGroup = new iBiz.FinPro.Transactions.Groups.objGroup(); iDB.Communicate dbComm = new iDB.Communicate(); IDataReader idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where transDrAccount=294 and transGroupId not in (select transGroupId from fin_TransGroups where transGroupPrefixString='AUT')"); if (idr != null) { while (idr.Read()) { int transId = Convert.ToInt32(idr["transId"]); objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objTrans = bTrans.Select(transId); if (objTrans != null) { int transGroupId = 0; objTGroup = new iBiz.FinPro.Transactions.Groups.objGroup(); objTGroup.transGroupTitle = "Credit Card Department Bank UBL DHA"; objTGroup.transGroupCreatedOn = objTrans.Get_Group().transGroupCreatedOn; objTGroup.transGroupCreatedBy = 1; objTGroup.transGroupTotalAmount = objTrans.transAmount; objTGroup.transTransCount = 2; objTGroup.transGroupPrefixNo = 7; objTGroup.transGroupPrefixString = "AUT"; objTGroup.transGroupForeNumber = objTrans.transGroupID; objTGroup.transGroupStatus = 1; objTGroup.transLinkedToGroup = objTrans.transGroupID; objTGroup.transGroupIsOfficial = 0; transGroupId = bTGroup.Add(objTGroup).Value; objDrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objDrTrans.deptId = objTrans.deptId; objDrTrans.transAmount = objTrans.transAmount; objDrTrans.transAttachedFiles = ""; objDrTrans.transCrAccount = null; objDrTrans.transCreatedBy = objTrans.transCreatedBy; objDrTrans.transCreatedOn = objTrans.Get_Group().transGroupCreatedOn; objDrTrans.transDrAccount = 301; objDrTrans.transGroupID = transGroupId; objDrTrans.transInvoiceID = 0; objDrTrans.transIsCompound = true; objDrTrans.transNarration = "Credit Card Sales UBL DHA"; objDrTrans.transParticipantID = 1; objDrTrans.transRefID = "0"; objDrTrans.transStatus = 1; objDrTrans.transSystemIndex = 2; objDrTrans.transSystemRestrict = false; objDrTrans.transType = 3; objDrTrans.transUpdatedBy = 1; objDrTrans.transUpdatedOn = objTrans.Get_Group().transGroupCreatedOn; bTrans.Add(objDrTrans); objCrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objCrTrans.deptId = objTrans.deptId; objCrTrans.transAmount = objTrans.transAmount; objCrTrans.transAttachedFiles = ""; objCrTrans.transCrAccount = 294; objCrTrans.transCreatedBy = objTrans.transCreatedBy; objCrTrans.transCreatedOn = objTrans.Get_Group().transGroupCreatedOn; objCrTrans.transDrAccount = null; objCrTrans.transGroupID = transGroupId; objCrTrans.transInvoiceID = 0; objCrTrans.transIsCompound = true; objCrTrans.transNarration = "Credit Card Sales UBL DHA"; objCrTrans.transParticipantID = 1; objCrTrans.transRefID = "0"; objCrTrans.transStatus = 1; objCrTrans.transSystemIndex = 2; objCrTrans.transSystemRestrict = false; objCrTrans.transType = 3; objCrTrans.transUpdatedBy = 1; objCrTrans.transUpdatedOn = objTrans.Get_Group().transGroupCreatedOn; bTrans.Add(objCrTrans); } } } idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where transDrAccount=296 and transGroupId not in (select transGroupId from fin_TransGroups where transGroupPrefixString='AUT')"); if (idr != null) { while (idr.Read()) { int transId = Convert.ToInt32(idr["transId"]); objTrans = bTrans.Select(transId); if (objTrans != null) { int transGroupId = 0; objTGroup = new iBiz.FinPro.Transactions.Groups.objGroup(); objTGroup.transGroupTitle = "Credit Card Department Bank UBL Gujrat"; objTGroup.transGroupCreatedOn = objTrans.Get_Group().transGroupCreatedOn; objTGroup.transGroupCreatedBy = 1; objTGroup.transGroupTotalAmount = objTrans.transAmount; objTGroup.transTransCount = 2; objTGroup.transGroupPrefixNo = 7; objTGroup.transGroupPrefixString = "AUT"; objTGroup.transGroupForeNumber = objTrans.transGroupID; objTGroup.transGroupStatus = 1; objTGroup.transLinkedToGroup = objTrans.transGroupID; objTGroup.transGroupIsOfficial = 0; transGroupId = bTGroup.Add(objTGroup).Value; objDrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objDrTrans.deptId = objTrans.deptId; objDrTrans.transAmount = objTrans.transAmount; objDrTrans.transAttachedFiles = ""; objDrTrans.transCrAccount = null; objDrTrans.transCreatedBy = objTrans.transCreatedBy; objDrTrans.transCreatedOn = objTrans.Get_Group().transGroupCreatedOn; objDrTrans.transDrAccount = 303; objDrTrans.transGroupID = transGroupId; objDrTrans.transInvoiceID = 0; objDrTrans.transIsCompound = true; objDrTrans.transNarration = "Credit Card Sales UBL Gujrat"; objDrTrans.transParticipantID = 1; objDrTrans.transRefID = "0"; objDrTrans.transStatus = 1; objDrTrans.transSystemIndex = 2; objDrTrans.transSystemRestrict = false; objDrTrans.transType = 3; objDrTrans.transUpdatedBy = 1; objDrTrans.transUpdatedOn = objTrans.Get_Group().transGroupCreatedOn; bTrans.Add(objDrTrans); objCrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction(); objCrTrans.deptId = objTrans.deptId; objCrTrans.transAmount = objTrans.transAmount; objCrTrans.transAttachedFiles = ""; objCrTrans.transCrAccount = 296; objCrTrans.transCreatedBy = objTrans.transCreatedBy; objCrTrans.transCreatedOn = objTrans.Get_Group().transGroupCreatedOn; objCrTrans.transDrAccount = null; objCrTrans.transGroupID = transGroupId; objCrTrans.transInvoiceID = 0; objCrTrans.transIsCompound = true; objCrTrans.transNarration = "Credit Card Sales UBL DHA"; objCrTrans.transParticipantID = 1; objCrTrans.transRefID = "0"; objCrTrans.transStatus = 1; objCrTrans.transSystemIndex = 2; objCrTrans.transSystemRestrict = false; objCrTrans.transType = 3; objCrTrans.transUpdatedBy = 1; objCrTrans.transUpdatedOn = objTrans.Get_Group().transGroupCreatedOn; bTrans.Add(objCrTrans); } } } }