private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (!CheckEntries())
{
return;
}
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtKeys = cnn.GetDataTable("select (select ifnull(max( convert( substring(jour_no,instr(jour_no,'-')+1),signed)),0)+1 FROM sales.journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " and trans_name='سند قيد') IssueNo,(select ifnull(max(pkid),0)+1 from sales.journal_header) pkid");
txtPkid.Text = dtKeys.Rows[0]["pkid"].ToString();
string strIssueNo = dtKeys.Rows[0]["IssueNo"].ToString();
txtJourNo.Text = glb_function.glb_strBranchPkid + "-" + strIssueNo;
int icheck = 0;
icheck = cnn.TranDataToDB("insert into journal_header values (" + txtPkid.Text +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + glb_function.glb_strBranchPkid +
",'" + txtJourNo.Text.Trim() + "'" +
",'سند قيد'" +
"," + strIssueNo +
",str_to_date('" + dtpJourDate.SelectedDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",'" + txtHeaderNote.Text.Trim() + "'" +
",null" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء حفظ البيانات الأساسية");
return;
}
for (int i = 0; i < dgvJourDetails.Items.Count; i++)
{
double dMainValue = 0;
double dCurrValue = 0;
if (Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmDept.DisplayIndex, i)) > 0)
{
dMainValue = Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmDept.DisplayIndex, i));
dCurrValue = Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmCurrValue.DisplayIndex, i));
}
else
{
dMainValue = Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmCredit.DisplayIndex, i)) * -1;
dCurrValue = Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmCurrValue.DisplayIndex, i)) * -1;
}
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + glb_function.GetCellValue(ref dgvJourDetails, clmCurrencyId.DisplayIndex, i) +
"," + glb_function.GetCellValue(ref dgvJourDetails, clmAccId.DisplayIndex, i) +
"," + dMainValue +
"," + dCurrValue +
"," + glb_function.GetCellValue(ref dgvJourDetails, clmExchangeRate.DisplayIndex, i) +
",'" + glb_function.GetCellValue(ref dgvJourDetails, clmJourNote.DisplayIndex, i) + "'" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
}
cnn.glb_commitTransaction();
GetData(txtPkid.Text);
if (glb_function.MsgBox("تمت عملية الحفظ بنجاح" + "\n" + "هل تريد طباعة سند القيد؟", "", true) == false)
{
return;
}
//SELECT worker_fname, worker_mname, worker_lname, worker_sirname," +
// "Passport_No,Passport_Place_Of_Birth_Id,date_format(Passport_Date_Of_Birth,'%Y-%m-%d')," +
// "date_format(Passport_Date_Of_Issue,'%Y-%m-%d'),date_format(Passport_Date_Of_Expiry,'%Y-%m-%d')" +
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!CheckEntries())
{
return;
}
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtKeys = cnn.GetDataTable("select (select ifnull(max(trans_id),0)+1 FROM journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " and trans_name='سند صرف') IssueNo,(select ifnull(max(pkid),0)+1 from journal_header) pkid,(select ifnull(max( convert( substring(jour_no,instr(jour_no,'-')+1),signed)),0)+1 FROM journal_header where Branch_id=" + glb_function.glb_strBranchPkid + ") JourNo");
txtPkid.Text = dtKeys.Rows[0]["pkid"].ToString();
string strIssueNo = dtKeys.Rows[0]["IssueNo"].ToString();
txtJourNo.Text = glb_function.glb_strBranchPkid + "-" + dtKeys.Rows[0]["JourNo"].ToString();
int icheck = 0;
icheck = cnn.TranDataToDB("insert into journal_header values (" + txtPkid.Text +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + glb_function.glb_strBranchPkid +
",'" + txtJourNo.Text.Trim() + "'" +
",'سند صرف'" +
"," + strIssueNo +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",'" + txtHeaderNote.Text.Trim() + "'" +
",'" + txtPerson.Text.Trim() + "'" +
",'" + glb_function.glb_strBranchPkid + "-" + strIssueNo + "'" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء حفظ البيانات الأساسية");
return;
}
//حفظ بيانات الدائن
//يكون الصندوق دائن في سند الصرف ويميزه انه اصغر من الصفر
double dDept = Convert.ToDouble(txtDeptTotal.Text) * -1;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + glb_function.glb_iMainCurrId +
"," + strCasherAccId +
"," + dDept +
"," + dDept +
",1" +
",'" + txtHeaderNote.Text.Trim() + "'" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
for (int i = 0; i < dgvJourDetails.Rows.Count; i++)
{
if (dgvJourDetails[clmAccId.Index, i].Value == null || dgvJourDetails[clmAccId.Index, i].Value.ToString() == "")
{
continue;
}
double dMainValue = 0;
double dCurrValue = 0;
if (Convert.ToDouble(dgvJourDetails[clmDept.Index, i].Value.ToString()) > 0)
{
dMainValue = Convert.ToDouble(dgvJourDetails[clmDept.Index, i].Value.ToString());
dCurrValue = dMainValue;
}
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + glb_function.glb_iMainCurrId +
"," + dgvJourDetails[clmAccId.Index, i].Value.ToString() +
"," + dMainValue +
"," + dCurrValue +
",1" +
",'" + dgvJourDetails[clmJourNote.Index, i].Value.ToString() + "'" +
",'" + dgvJourDetails[clmProfitCenter.Index, i].Value.ToString() + "'" +
",'" + dgvJourDetails[clmProfitCenter1.Index, i].Value.ToString() + "'" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
}
cnn.glb_commitTransaction();
GetData(txtPkid.Text);
glb_function.MsgBox("تمت عملية الحفظ بنجاح");
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtKeys = cnn.GetDataTable("select (select ifnull(max( convert( substring(jour_no,instr(jour_no,'-')+1),signed)),0)+1 FROM sales.journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " and trans_name='سند صرف') IssueNo,(select ifnull(max(pkid),0)+1 from sales.journal_header) pkid");
txtPkid.Text = dtKeys.Rows[0]["pkid"].ToString();
string strIssueNo = dtKeys.Rows[0]["IssueNo"].ToString();
txtJourNo.Text = glb_function.glb_strBranchPkid + "-" + strIssueNo;
int icheck = 0;
icheck = cnn.TranDataToDB("insert into journal_header values (" + txtPkid.Text +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + glb_function.glb_strBranchPkid +
",'" + txtJourNo.Text.Trim() + "'" +
",'سند صرف'" +
"," + strIssueNo +
",str_to_date('" + dtpJourDate.SelectedDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",'" + txtHeaderNote.Text.Trim() + "'" +
",'" + txtPerson.Text.Trim() + "'" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء حفظ البيانات الأساسية");
return;
}
//حفظ بيانات المدين
//
double dMainValue = (double)nmbDept.Value;
double dCurrValue = (double)nmbCurrValue.Value;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + lstCurrency.SelectedValue.ToString() +
"," + lstAccNo.SelectedValue.ToString() +
"," + dMainValue +
"," + dCurrValue +
"," + nmbExchangeRate.Value.ToString() +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
//حفظ بيانات الدائن
//يكون الصندوق دائن في سند الصرف ويميزه انه اصغر من الصفر
dMainValue = (double)nmbDept.Value * -1;
dCurrValue = (double)nmbCurrValue.Value * -1;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + lstCurrency.SelectedValue.ToString() +
"," + strCasherAccId +
"," + dMainValue +
"," + dCurrValue +
"," + nmbExchangeRate.Value.ToString() +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
cnn.glb_commitTransaction();
GetData(txtPkid.Text);
if (glb_function.MsgBox("تمت عملية الحفظ بنجاح" + "\n" + "هل تريد طباعة سند القبض؟", "", true) == false)
{
return;
}
//SELECT worker_fname, worker_mname, worker_lname, worker_sirname," +
// "Passport_No,Passport_Place_Of_Birth_Id,date_format(Passport_Date_Of_Birth,'%Y-%m-%d')," +
// "date_format(Passport_Date_Of_Issue,'%Y-%m-%d'),date_format(Passport_Date_Of_Expiry,'%Y-%m-%d')" +
}
private void btnSave_Click(object sender, EventArgs e)
{
if (txtInvoiceNo.Text.Trim() == "")
{
glb_function.MsgBox("الرجاء ادخال رقم الفاتورة");
txtInvoiceNo.Focus();
return;
}
if (lstWarehouse.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار المخزن");
lstWarehouse.Focus();
return;
}
if (lstAccNo.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار رقم الحساب");
lstAccNo.Focus();
return;
}
if (lstAccName.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار اسم الحساب");
lstAccName.Focus();
return;
}
if (dgvItems.Rows.Count <= 0)
{
glb_function.MsgBox("الرجاء ادخال صنف واحد على الاقل");
return;
}
for (int i = 0; i < dgvItems.Rows.Count; i++)
{
if (dgvItems[clmItemId.Index, i].Value == null || dgvItems[clmItemId.Index, i].Value.ToString() == "")
{
continue;
}
if (nmbExpTotal.Value > 0)
{
//صيب الريال الواحد = مصاريف الشحن / قيمة البضاعة
double dRY_share = (double)(nmbExpTotal.Value / nmbItemTotal.Value);
//إذن نصيب الوحدة الواحدة من تكلفة الشحن
double dQty = Convert.ToDouble(dgvItems[clmItemQty.Index, i].Value.ToString());
double dUnitPrice = Convert.ToDouble(dgvItems[clmItemPrice.Index, i].Value.ToString());
double UnitCostShare = dUnitPrice * dRY_share;
//تكلفة الصنف
double dUnitCost = UnitCostShare + dUnitPrice;
dgvItems[clmItemCost.Index, i].Value = dUnitCost.ToString();
}
else
{
double dUnitPrice = Convert.ToDouble(dgvItems[clmItemPrice.Index, i].Value.ToString());
dgvItems[clmItemCost.Index, i].Value = dUnitPrice.ToString();
}
}
ConnectionToMySQL cnnSave = new ConnectionToMySQL();
DataTable dt = cnnSave.GetDataTable("(select ifnull(max(b.pkid),0)+1 from pi_header b)");
txtPkid.Text = dt.Rows[0][0].ToString();
int icheck = cnnSave.TranDataToDB("insert into pi_header " +
" values(" + txtPkid.Text + "" +
",'فعال'" +
",SYSDATE() " +
"," + glb_function.glb_strUserId +
",'" + txtInvoiceNo.Text.Trim() + "'" +
"," + lstWarehouse.SelectedValue.ToString() +
"," + lstAccNo.SelectedValue.ToString() +
",'" + txtInvoiceNote.Text.Trim() + "'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات فاتورة الشراء");
return;
}
for (int i = 0; i < dgvItems.Rows.Count; i++)
{
if (dgvItems[clmItemId.Index, i].Value == null || dgvItems[clmItemId.Index, i].Value.ToString() == "")
{
continue;
}
icheck = cnnSave.TranDataToDB("insert into pi_details values ((select ifnull(max(b.pkid),0)+1 from pi_details b)" +
"," + txtPkid.Text +
"," + dgvItems[clmItemId.Index, i].Value.ToString() +
"," + dgvItems[clmItemQty.Index, i].Value.ToString() +
"," + dgvItems[clmItemPrice.Index, i].Value.ToString() +
"," + dgvItems[clmItemCost.Index, i].Value.ToString() +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات الصنف");
return;
}
//تغيير تكلفة الصنف
double dQty = Convert.ToDouble(dgvItems[clmItemQty.Index, i].Value.ToString());
double dOldunitCost = Convert.ToDouble(dgvItems[clmOldUnitCost.Index, i].Value.ToString());
double dQtyonhand = Convert.ToDouble(dgvItems[clmQtyOnhand.Index, i].Value.ToString());
double dRequiredQty = Convert.ToDouble(dgvItems[clmItemQty.Index, i].Value.ToString());
double dUnitCost = Convert.ToDouble(dgvItems[clmItemCost.Index, i].Value.ToString());
double dNewCost = ((dOldunitCost * dQtyonhand) + (dRequiredQty * dUnitCost)) / (dQtyonhand + dRequiredQty);
icheck = cnnSave.TranDataToDB("update items set unitcost=" + dNewCost.ToString() + " where pkid=" + dgvItems[clmItemId.Index, i].Value.ToString());
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات حركة الاصناف");
return;
}
//اضافة الكمية الى المخزون
icheck = cnnSave.TranDataToDB("insert into item_trans values ((select ifnull(max(b.pkid),0)+1 from item_trans b)" +
",SYSDATE() " +
"," + glb_function.glb_strUserId +
"," + dgvItems[clmItemId.Index, i].Value.ToString() +
"," + lstWarehouse.SelectedValue.ToString() +
"," + dQty +
"," + txtPkid.Text +
",'فاتورة شراء'" +
",''" +
",'" + txtInvoiceNo.Text.Trim() + "'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات حركة الاصناف");
return;
}
}//end item for loop
//تقييد عملية الشراء في الحسابات
#region InventoryOrderIn
DataTable dtKeys = cnnSave.GetDataTable("select (select ifnull(max( convert( substring(jour_no,instr(jour_no,'-')+1),signed)),0)+1 FROM journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " ) JourNo,(select ifnull(max(pkid),0)+1 from journal_header) pkid");
string strJPkid = dtKeys.Rows[0]["pkid"].ToString();
string strJourNo = glb_function.glb_strBranchPkid + "-" + dtKeys.Rows[0]["JourNo"].ToString();
icheck = 0;
icheck = cnnSave.TranDataToDB("insert into journal_header values (" + strJPkid +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + glb_function.glb_strBranchPkid +
",'" + strJourNo.Trim() + "'" +
",'فاتورة شراء'" +
"," + txtPkid.Text +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",'قيدت من فاتورة المشتريات'" +
",''" +
",'" + txtInvoiceNo.Text.Trim() + "'" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ البيانات الأساسية");
return;
}
//حفظ بيانات المدين
//يكون المخزون مدين في فاتورة الشراء ويميزه انه اكبر من الصفر
double dMainValue = (double)(nmbItemTotal.Value + nmbExpTotal.Value);
double dCurrValue = dMainValue;
icheck = cnnSave.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + strInventoryAccId +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
//حفظ بيانات الدائن
//
dMainValue = (double)nmbItemTotal.Value * -1;
dCurrValue = dMainValue;
icheck = cnnSave.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + lstAccNo.SelectedValue.ToString() +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
for (int j = 0; j < dgvExpensses.Rows.Count; j++)
{
if (dgvExpensses[clmAccId.Index, j].Value == null || dgvExpensses[clmAccId.Index, j].Value.ToString() == "")
{
continue;
}
//حفظ قيد المصاريف على الحاسب المحدد للمصروف
dMainValue = Convert.ToDouble(dgvExpensses[clmExpValue.Index, j].Value.ToString()) * -1;
dCurrValue = dMainValue;
icheck = cnnSave.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + dgvExpensses[clmAccId.Index, j].Value.ToString() +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
//حفظ بيانات المصاريف في جدول المصاريف التابع لفاتورة الشراء
//pi_expensses
icheck = cnnSave.TranDataToDB("insert into pi_expensses values ((select ifnull(max(b.pkid),0)+1 from pi_expensses b)" +
"," + txtPkid.Text +
",'" + dgvExpensses[clmExpName.Index, j].Value.ToString() + "'" +
"," + dgvExpensses[clmExpValue.Index, j].Value.ToString() +
"," + dgvExpensses[clmAccId.Index, j].Value.ToString() +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات الصنف");
return;
}
}
#endregion
cnnSave.glb_commitTransaction();
glb_function.MsgBox("تمت العملية بنجاح");
GetData(txtPkid.Text);
}
private void dgProperities_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
if (e.ColumnIndex == 2)
{
lstEditor.DataSource = null;
lstEditor.Items.Clear();
if (dgProperities[3, e.RowIndex].Value.ToString() == "Enability")
{
lstEditor.Items.Add("تفعيل");
lstEditor.Items.Add("اغلاق");
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "Visability")
{
lstEditor.Items.Add("اظهار");
lstEditor.Items.Add("اخفاء");
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "Text")
{
return;
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "Free List")
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtFreeList = cnn.GetDataTable("select display_member,value_member from free_list " +
" where list_name = '" + dgProperities[5, e.RowIndex].Value.ToString() + "'");
lstEditor.DataSource = dtFreeList;
lstEditor.ValueMember = "value_member";
lstEditor.DisplayMember = "display_member";
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "List")
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtListTable = cnn.GetDataTable("select table_name,display_member,value_member from LIST_TYPE t " +
" where pkid = '" + dgProperities[4, e.RowIndex].Value.ToString() + "'");
DataTable dtGetList = cnn.GetDataTable("select " + dtListTable.Rows[0]["display_member"].ToString() + " name," +
dtListTable.Rows[0]["value_member"].ToString() +
" id from " + dtListTable.Rows[0]["table_name"].ToString() +
" ");
lstEditor.DataSource = dtGetList;
lstEditor.ValueMember = "id";
lstEditor.DisplayMember = "name";
if (dgProperities[5, e.RowIndex].Value.ToString() != "")
{
DataTable dtFreeList = cnn.GetDataTable("select display_member,value_member from free_list " +
" where list_name = '" + dgProperities[5, e.RowIndex].Value.ToString() + "'");
DataRow dr;
for (int i = 0; i < dtFreeList.Rows.Count; i++)
{
string str1 = dtFreeList.Rows[i]["display_member"].ToString();
string str2 = dtFreeList.Rows[i]["value_member"].ToString();
dr = dtGetList.NewRow();
dr["name"] = dtFreeList.Rows[i]["display_member"].ToString();
dr["id"] = Convert.ToInt16(dtFreeList.Rows[i]["value_member"].ToString());
dtGetList.Rows.InsertAt(dr, 0);
}
}
}
else
{
glb_function.MsgBox("قيمة غير معروفة");
return;
}
DataGridViewCell abc = dgProperities.CurrentCell;
Rectangle RECT = dgProperities.GetCellDisplayRectangle(abc.ColumnIndex, abc.RowIndex, true);
if (dgProperities[2, e.RowIndex].Value.ToString() != "")
{
lstEditor.Text = dgProperities[2, e.RowIndex].Value.ToString();
}
lstEditor.Tag = e.RowIndex.ToString();
CtlLocation(RECT);
}
else if (e.ColumnIndex == 8)
{
if (dgProperities[2, e.RowIndex].Value.ToString() == "")
{
glb_function.MsgBox("الرجاء ادخال القيمة");
return;
}
if (dgvTempDetails.Rows.Count <= 0)
{
glb_function.MsgBox("الرجاء تحديد النموذج");
return;
}
ConnectionToMySQL cnn = new ConnectionToMySQL();
int icheck = 0;
if (glb_function.MsgBox("هل انت متاكد من عملية التعديل", "", true))
{
for (int i = 0; i < dgvTempDetails.Rows.Count; i++)
{
if (dgvTempDetails[4, i].Value.ToString() == dgProperities[6, e.RowIndex].Value.ToString())
{
icheck = cnn.TranDataToDB("delete from TEMPLET_DETAILS where pkid=" + dgvTempDetails[0, i].Value.ToString());
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطا اثناء العملية");
return;
}
}
}
icheck = cnn.TranDataToDB("insert into TEMPLET_DETAILS values((select ifnull(max(pkid),0)+1 from TEMPLET_DETAILS)" +
"," + dgvTempHeader[0, dgvTempHeader.CurrentRow.Index].Value.ToString() +
",'" + dgProperities[3, e.RowIndex].Value.ToString() + "','" + dgProperities[6, e.RowIndex].Value.ToString() + "','" + dgProperities[1, e.RowIndex].Value.ToString() + "','" + dgProperities[2, e.RowIndex].Value.ToString() + "'," + glb_function.glb_strUserId + ",sysdate,'" + (dgProperities[7, e.RowIndex].Value == null ? dgProperities[2, e.RowIndex].Value.ToString() : dgProperities[7, e.RowIndex].Value.ToString()) + "')");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطا اثناء العملية");
return;
}
cnn.glb_commitTransaction();
glb_function.MsgBox("تمت العملية بنجاح.");
GetTempDetailsData(dgvTempHeader.CurrentRow.Index);
}
}
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
cryRepJournals report = new cryRepJournals();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
double dBalance = 0;
string strDate = "";
string strStat = "";
if (ckbPosting.Checked == true)
{
strStat = " and h.stat in ('فعال', 'مرحل')";
}
else
{
strStat = " and h.stat in ( 'مرحل')";
}
if (txtJourNo.Text.Trim() != "")
{
strStat += " and jour_no='" + txtJourNo.Text.Trim() + "'";
}
if (ckbSelectDate.Checked == true)
{
strDate = " and jour_date between str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpTo.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
dtReport = cnn.GetDataTable("select ifnull(sum(main_value) ,0) balance " +
" from journal_header h " +
" join journal_details d on (h.pkid = d.header_id) " +
" where h.created_date < str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y') " + strStat);
dBalance = Convert.ToDouble(dtReport.Rows[0][0].ToString());
}
else
{
dBalance = 0;
}
dtReport.Clear();
dtReport = cnn.GetDataTable("select h.pkid,h.jour_no,h.trans_no,h.trans_name,date_format(h.jour_date,'%d/%m/%Y') jour_date,h.jour_note, " +
" d.acc_id,a.Acc_no,a.acc_name," +
" if (d.main_value > 0,d.main_value,0) Dept,if (d.main_value < 0,d.main_value * -1,0) Credit,d.jour_details,0 Balance, 'مدين' BalStat" +
" from journal_header h " +
" join journal_details d on(h.pkid= d.header_id)" +
" join accounts a on (d.acc_id=a.pkid)" +
" where 1=1 " + strStat + strDate + " order by h.pkid,main_value desc");
report.SetDataSource(dtReport);
report.SetParameterValue("From", (ckbSelectDate.Checked == false ? "" : dtpFrom.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("To", (ckbSelectDate.Checked == false ? DateTime.Now.ToString("dd/MM/yyyy") : dtpTo.Value.ToString("dd/MM/yyyy")));
// report.SetParameterValue("strValueAlpha", new ConvertNumbersToArabicAlphabet(txtCreditTotal.Text).GetNumberAr());
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnUpdate_Click(object sender, RoutedEventArgs e)
{
if (txtPkid.Text.Trim() == "")
{
glb_function.MsgBox("الرجاء اختيار مستخدم");
return;
}
if (txtUserName.Text.Trim() == "")
{
glb_function.MsgBox("الرجاء ادخال اسم المستخدم");
txtUserName.Focus();
return;
}
if (txtUserLogin.Text.Trim() == "")
{
glb_function.MsgBox("الرجاء ادخال رمز الدخول");
txtUserLogin.Focus();
return;
}
if (lstBranches.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار الفرع");
lstBranches.Focus();
return;
}
ConnectionToMySQL cnnSave = new ConnectionToMySQL();
int icheck = cnnSave.TranDataToDB("update users " +
" set UserLoginName='" + txtUserLogin.Text.Trim() + "',userLoginEncry='" + new glb_function().Encrypt(txtUserLogin.Text.Trim(), true) + "',UserFullName='" + txtUserName.Text.Trim() +
"',Notes='" + txtNote.Text.Trim() + "',branch_id=" + lstBranches.SelectedValue.ToString() + " where pkid=" + txtPkid.Text);
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات المستخدم");
return;
}
icheck = cnnSave.TranDataToDB("delete from USER_TEMPLET where user_id=" + txtPkid.Text);
if (icheck < 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات المستخدم");
return;
}
GetTempletToSave();
while (strSelectedTemp != "")
{
string strTemp = strSelectedTemp.Substring(0, strSelectedTemp.IndexOf(';'));
icheck = cnnSave.TranDataToDB("insert into USER_TEMPLET values ((select ifnull(max(b.pkid),0)+1 from USER_TEMPLET b), " + txtPkid.Text + "," + strTemp + ")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية التعديل بيانات المستخدم");
return;
}
strSelectedTemp = strSelectedTemp.Substring(strSelectedTemp.IndexOf(';') + 1);
}
cnnSave.glb_commitTransaction();
glb_function.MsgBox("تمت العملية بنجاح");
UpdateHistory();
GetData(txtPkid.Text);
}
private void btnPrint_Click(object sender, EventArgs e)
{
cryRepTrialBalance report = new cryRepTrialBalance();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
string strDate = "";
string strPrevDate = "";
string strStat = "";
if (ckbPosting.Checked == true)
{
strStat = " and h.stat in ('فعال', 'مرحل')";
}
else
{
strStat = " and h.stat in ( 'مرحل')";
}
if (ckbClosingEntry.Checked == false)
{
strStat += " and h.trans_name!='سند اقفال'";
}
if (ckbSelectDate.Checked == true)
{
strDate = " and h.trans_name!='قيد افتتاحي' and jour_date between str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpTo.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
if (dtpFrom.Value.ToString("dd/MM") == "01/01")
{
strPrevDate = " and h.trans_name='قيد افتتاحي'";
}
else
{
strPrevDate = " and jour_date between str_to_date('01/01/" + dtpFrom.Value.ToString("yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpFrom.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
}
}
if (strPrevDate == "")
{
// strPrevDate = " and jour_date between str_to_date('01/01/1999', '%d/%m/%Y') and str_to_date('02/01/1999', '%d/%m/%Y')";
strDate = " and h.trans_name!='قيد افتتاحي'";
strPrevDate = " and h.trans_name='قيد افتتاحي'";
}
dtReport.Clear();
dtReport = cnn.GetDataTable("SELECT Acc_no,acc_name ," +
"(select sum(main_value) " +
" from journal_header h " +
" join journal_details d on (d.header_id = h.Pkid) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where main_value > 0 " +
" and acc_no like concat(acc.acc_no, '%') " + strDate + strStat + ") dept, " +
"(select sum(main_value) " +
" from journal_header h " +
" join journal_details d on (d.header_id = h.Pkid) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where main_value > 0 " +
" and acc_no like concat(acc.acc_no, '%') " + strPrevDate + strStat + ") Prevdept, " +
" (select sum(main_value) * -1 " +
" from journal_header h " +
" join journal_details d on (d.header_id = h.Pkid) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where main_value < 0 " +
" and acc_no like concat(acc.acc_no,'%')" + strDate + strStat + ") credit, " +
" (select sum(main_value) * -1 " +
" from journal_header h " +
" join journal_details d on (d.header_id = h.Pkid) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where main_value < 0 " +
" and acc_no like concat(acc.acc_no,'%')" + strPrevDate + strStat + ") Prevcredit, " +
" 0.00 balance_dept,0.00 balance_credit " +
" FROM accounts acc " +
" order by acc_no desc");
decimal dDept = 0;
decimal dCredit = 0;
decimal dPreDept = 0;
decimal dPreCredit = 0;
decimal dBalance = 0;
for (int i = 0; i < dtReport.Rows.Count; i++)
{
if (dtReport.Rows[i]["Acc_no"].ToString() == "11102")
{
int x = 1;
}
if (dtReport.Rows[i]["dept"].ToString() == "")
{
dDept = 0;
dtReport.Rows[i]["dept"] = "0";
}
else
{
dDept = Convert.ToDecimal(dtReport.Rows[i]["dept"].ToString());
}
if (dtReport.Rows[i]["Prevdept"].ToString() == "")
{
dtReport.Rows[i]["Prevdept"] = "0";
dPreDept = 0;
}
else
{
dPreDept = Convert.ToDecimal(dtReport.Rows[i]["Prevdept"].ToString());
}
if (dtReport.Rows[i]["credit"].ToString() == "")
{
dCredit = 0;
dtReport.Rows[i]["credit"] = "0";
}
else
{
dCredit = Convert.ToDecimal(dtReport.Rows[i]["credit"].ToString());
}
if (dtReport.Rows[i]["Prevcredit"].ToString() == "")
{
dtReport.Rows[i]["Prevcredit"] = "0";
dPreCredit = 0;
}
else
{
dPreCredit = Convert.ToDecimal(dtReport.Rows[i]["Prevcredit"].ToString());
}
if ((dDept + dPreDept) - (dCredit + dPreCredit) == 0)
{
dtReport.Rows.Remove(dtReport.Rows[i]);
i--;
}
else
{
dBalance = (dDept + dPreDept) - (dCredit + dPreCredit);
if (dBalance < 0)
{
dtReport.Rows[i]["balance_credit"] = (dBalance * -1).ToString();
}
else
{
dtReport.Rows[i]["balance_dept"] = (dBalance).ToString();
}
}
}
report.SetDataSource(dtReport);
report.SetParameterValue("From", (ckbSelectDate.Checked == false ? "" : dtpFrom.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("To", (ckbSelectDate.Checked == false ? DateTime.Now.ToString("dd/MM/yyyy") : dtpTo.Value.ToString("dd/MM/yyyy")));
// report.SetParameterValue("strValueAlpha", new ConvertNumbersToArabicAlphabet(txtCreditTotal.Text).GetNumberAr());
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void dgProperities_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (dgvForms.Items.Count > 0)
{
if (dgProperities.SelectedIndex >= 0)
{
if (glb_function.GetCellValue(ref dgProperities, clmcontrol_type.DisplayIndex, dgProperities.SelectedIndex) == "Enability")
{
frmlstEditor frm = new frmlstEditor();
frm.lstEditor.Items.Add("تفعيل");
frm.lstEditor.Items.Add("اغلاق");
frm.ShowDialog();
clsFormDetails UpdateRow = (clsFormDetails)dgProperities.Items[dgProperities.SelectedIndex];
UpdateRow.clmPropertyValue = frm.lstEditor.Text;
if (frm.lstEditor.Text == "تفعيل")
{
UpdateRow.clmRealValue = "1";
}
else
{
UpdateRow.clmRealValue = "0";
}
int iSelectedIndex = dgProperities.SelectedIndex;
dgProperities.Items[iSelectedIndex] = UpdateRow;
dgProperities.Items.Refresh();
}
else if (glb_function.GetCellValue(ref dgProperities, clmcontrol_type.DisplayIndex, dgProperities.SelectedIndex) == "Visability")
{
frmlstEditor frm = new frmlstEditor();
frm.lstEditor.Items.Add("اظهار");
frm.lstEditor.Items.Add("اخفاء");
frm.ShowDialog();
clsFormDetails UpdateRow = (clsFormDetails)dgProperities.Items[dgProperities.SelectedIndex];
UpdateRow.clmPropertyValue = frm.lstEditor.Text;
if (frm.lstEditor.Text == "اظهار")
{
UpdateRow.clmRealValue = "1";
}
else
{
UpdateRow.clmRealValue = "0";
}
int iSelectedIndex = dgProperities.SelectedIndex;
dgProperities.Items[iSelectedIndex] = UpdateRow;
dgProperities.Items.Refresh();
}
else if (glb_function.GetCellValue(ref dgProperities, clmcontrol_type.DisplayIndex, dgProperities.SelectedIndex) == "Text")
{
DataGridRow row = dgProperities.ItemContainerGenerator.ContainerFromIndex(dgProperities.SelectedIndex) as DataGridRow;
ContentPresenter CP = dgProperities.Columns[clmPropertyValue.DisplayIndex].GetCellContent(row) as ContentPresenter;
TextBox DP = glb_function.FindVisualChild <TextBox>(CP);
DP.IsReadOnly = false;
}
else if (glb_function.GetCellValue(ref dgProperities, clmcontrol_type.DisplayIndex, dgProperities.SelectedIndex) == "Free List")
{
frmlstEditor frm = new frmlstEditor();
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtFreeList = cnn.GetDataTable("select display_member,value_member from free_list " +
" where list_name = '" + glb_function.GetCellValue(ref dgProperities, clmFreeListName.DisplayIndex, dgProperities.SelectedIndex) + "'");
frm.lstEditor.ItemsSource = dtFreeList.DefaultView;
frm.lstEditor.SelectedValuePath = "value_member";
frm.lstEditor.DisplayMemberPath = "display_member";
frm.ShowDialog();
clsFormDetails UpdateRow = (clsFormDetails)dgProperities.Items[dgProperities.SelectedIndex];
UpdateRow.clmPropertyValue = frm.lstEditor.Text;
UpdateRow.clmRealValue = frm.lstEditor.SelectedValue.ToString();
int iSelectedIndex = dgProperities.SelectedIndex;
dgProperities.Items[iSelectedIndex] = UpdateRow;
dgProperities.Items.Refresh();
}
else if (glb_function.GetCellValue(ref dgProperities, clmcontrol_type.DisplayIndex, dgProperities.SelectedIndex) == "List")
{
frmlstEditor frm = new frmlstEditor();
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtListTable = cnn.GetDataTable("select select_statement from LIST_TYPE t " +
" where pkid = '" + glb_function.GetCellValue(ref dgProperities, clmListId.DisplayIndex, dgProperities.SelectedIndex) + "'");
DataTable dtGetList = cnn.GetDataTable(dtListTable.Rows[0][0].ToString());
frm.lstEditor.ItemsSource = dtGetList.DefaultView;
frm.lstEditor.SelectedValuePath = "pkid";
frm.lstEditor.DisplayMemberPath = "name";
frm.ShowDialog();
clsFormDetails UpdateRow = (clsFormDetails)dgProperities.Items[dgProperities.SelectedIndex];
UpdateRow.clmPropertyValue = frm.lstEditor.Text;
UpdateRow.clmRealValue = frm.lstEditor.SelectedValue.ToString();
int iSelectedIndex = dgProperities.SelectedIndex;
dgProperities.Items[iSelectedIndex] = UpdateRow;
dgProperities.Items.Refresh();
}
else if (glb_function.GetCellValue(ref dgProperities, clmcontrol_type.DisplayIndex, dgProperities.SelectedIndex) == "MultiChoices")
{
frmMultilstEditor frm = new frmMultilstEditor();
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtListTable = cnn.GetDataTable("select select_statement from LIST_TYPE t " +
" where pkid = '" + glb_function.GetCellValue(ref dgProperities, clmListId.DisplayIndex, dgProperities.SelectedIndex) + "'");
DataTable dtGetList = cnn.GetDataTable(dtListTable.Rows[0][0].ToString());
//frm.lstEditor.ItemsSource = dtGetList.DefaultView;
//frm.lstEditor.SelectedValuePath = "pkid".ToUpper();
//frm.lstEditor.DisplayMemberPath = "name".ToUpper();
frm.myList = new ObservableCollection <frmMultilstEditor.MultiList>();
for (int i = 0; i < dtGetList.Rows.Count; i++)
{
string strText = dtGetList.Rows[i]["name"].ToString();
int iValue = Convert.ToUInt16(dtGetList.Rows[i]["pkid"].ToString());
frm.myList.Add(new frmMultilstEditor.MultiList {
TheText = strText, TheValue = iValue
});
}
frm.DataContext = frm;
frm.lstEditor.UpdateLayout();
if (glb_function.GetCellValue(ref dgProperities, clmRealValue.DisplayIndex, dgProperities.SelectedIndex) != "")
{
string strMult = glb_function.GetCellValue(ref dgProperities, clmRealValue.DisplayIndex, dgProperities.SelectedIndex);
string iindexx = "";
while (strMult != "")
{
iindexx = (strMult.Substring(0, strMult.IndexOf(";")));
strMult = strMult.Substring(strMult.IndexOf(";") + 1);
for (int i = 0; i < frm.lstEditor.Items.Count; i++)
{
frmMultilstEditor.MultiList o = (frmMultilstEditor.MultiList)frm.lstEditor.Items[i];
if (o.TheValue.ToString() == iindexx)
{
o.IsSelected = true;
}
}
}
}
frm.ShowDialog();
string strPropertyValue = "";
string strPropRealValue = "";
for (int i = 0; i < frm.lstEditor.Items.Count; i++)
{
frmMultilstEditor.MultiList o = (frmMultilstEditor.MultiList)frm.lstEditor.Items[i];
if (o.IsSelected == true)
{
strPropertyValue += o.TheText + ";";
strPropRealValue += o.TheValue + ";";
}
}
clsFormDetails UpdateRow = (clsFormDetails)dgProperities.Items[dgProperities.SelectedIndex];
UpdateRow.clmPropertyValue = strPropertyValue;
UpdateRow.clmRealValue = strPropRealValue;
int iSelectedIndex = dgProperities.SelectedIndex;
dgProperities.Items[iSelectedIndex] = UpdateRow;
dgProperities.Items.Refresh();
}
else if (glb_function.GetCellValue(ref dgProperities, clmcontrol_type.DisplayIndex, dgProperities.SelectedIndex) == "Free MultiChoices")
{
frmMultilstEditor frm = new frmMultilstEditor();
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtFreeList = cnn.GetDataTable("select display_member,value_member from free_list " +
" where list_name = '" + glb_function.GetCellValue(ref dgProperities, clmFreeListName.DisplayIndex, dgProperities.SelectedIndex) + "'");
frm.myList = new ObservableCollection <frmMultilstEditor.MultiList>();
for (int i = 0; i < dtFreeList.Rows.Count; i++)
{
string strText = dtFreeList.Rows[i]["name"].ToString();
int iValue = Convert.ToUInt16(dtFreeList.Rows[i]["pkid"].ToString());
frm.myList.Add(new frmMultilstEditor.MultiList {
TheText = strText, TheValue = iValue
});
}
frm.DataContext = frm;
frm.lstEditor.UpdateLayout();
if (glb_function.GetCellValue(ref dgProperities, clmRealValue.DisplayIndex, dgProperities.SelectedIndex) != "")
{
string strMult = glb_function.GetCellValue(ref dgProperities, clmRealValue.DisplayIndex, dgProperities.SelectedIndex);
string iindexx = "";
while (strMult != "")
{
iindexx = (strMult.Substring(0, strMult.IndexOf(";")));
strMult = strMult.Substring(strMult.IndexOf(";") + 1);
for (int i = 0; i < frm.lstEditor.Items.Count; i++)
{
frmMultilstEditor.MultiList o = (frmMultilstEditor.MultiList)frm.lstEditor.Items[i];
if (o.TheValue.ToString() == iindexx)
{
o.IsSelected = true;
}
}
}
}
frm.ShowDialog();
string strPropertyValue = "";
string strPropRealValue = "";
for (int i = 0; i < frm.lstEditor.Items.Count; i++)
{
frmMultilstEditor.MultiList o = (frmMultilstEditor.MultiList)frm.lstEditor.Items[i];
if (o.IsSelected == true)
{
strPropertyValue += o.TheText + ";";
strPropRealValue += o.TheValue + ";";
}
}
clsFormDetails UpdateRow = (clsFormDetails)dgProperities.Items[dgProperities.SelectedIndex];
UpdateRow.clmPropertyValue = strPropertyValue;
UpdateRow.clmRealValue = strPropRealValue;
int iSelectedIndex = dgProperities.SelectedIndex;
dgProperities.Items[iSelectedIndex] = UpdateRow;
dgProperities.Items.Refresh();
}
}
}
}
private void button3_Click(object sender, EventArgs e)
{
glb_function.strDbCnnString = "server=localhost;port=3300;charset=utf8;database=ammar2016;userid=root;password=bigboss;SslMode=none;AllowPublicKeyRetrieval=True";
ConnectionToMySQL cnn = new ConnectionToMySQL();
int icheck = 0;
icheck = cnn.TranDataToDB("insert into journal_header values(1" +
",'مرحل'" +
",sysdate()" +
",1" +
",1" +
",'1-1'" +
",'قيد افتتاحي'" +
",1" +
",str_to_date('01/01/2016',' %d/%m/%Y')" +
",'الرصيد الافتتاحي 01/ 01/ 2016م'" +
",null" +
",'1-1'" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("Error");
return;
}
string strAmount = "";
for (int i = 0; i < myDataGrid1.Rows.Count; i++)
{
if (myDataGrid1[6, i].Value.ToString().Trim() == "0")
{
strAmount = "-" + myDataGrid1[7, i].Value.ToString().Trim();
}
else
{
strAmount = myDataGrid1[6, i].Value.ToString().Trim();
}
icheck = cnn.TranDataToDB("insert into journal_details values((select ifnull(max(b.pkid),0)+1 from journal_details b)" +
",'فعال'" +
",sysdate()" +
",1" +
",1" +
",1" +
"," + myDataGrid1[3, i].Value.ToString() +
"," + strAmount +
"," + strAmount +
",1" +
",'الرصيد الافتتاحي 01/ 01/ 2016م'" +
",'" + myDataGrid1[13, i].Value.ToString() + "'" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("Error");
return;
}
}
cnn.glb_commitTransaction();
glb_function.MsgBox("Save");
}
private void mainButton2_Click(object sender, EventArgs e)
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
dtReport = cnn.GetDataTable("select item_id,itemname,warehouse_id, warehouse_name, " +
" ifnull((select sum(qty) from item_trans PrevT where PrevT.item_id = trans.item_id and PrevT.warehouse_id = trans.warehouse_id and PrevT.trans_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and PrevT.qty > 0),0) TodayInQty, " +
" ifnull((select sum(qty) from item_trans PrevT where PrevT.item_id = trans.item_id and PrevT.warehouse_id = trans.warehouse_id and PrevT.trans_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and PrevT.qty < 0),0) TodayOutQty, " +
" ifnull((select sum(qty) from item_trans PrevT where PrevT.item_id = trans.item_id and PrevT.warehouse_id = trans.warehouse_id and PrevT.trans_date < str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and PrevT.qty > 0),0) PostingInQty, " +
" ifnull((select sum(qty) from item_trans PrevT where PrevT.item_id = trans.item_id and PrevT.warehouse_id = trans.warehouse_id and PrevT.trans_date < str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and PrevT.qty < 0),0) PostingOutQty," +
"0 TotalInQty,0 TotalOutQty,0 RemaindQty,'1' linkR" +
" from " +
" (SELECT item_id, warehouse_id " +
" FROM item_trans " +
" group by item_id, warehouse_id) trans " +
" join warehouse w on(w.pkid = trans.warehouse_id) " +
" join items i on(i.pkid= trans.item_id)");
for (int i = 0; i < dtReport.Rows.Count; i++)
{
dtReport.Rows[i]["TodayOutQty"] = Convert.ToDecimal(dtReport.Rows[i]["TodayOutQty"].ToString()) * -1;
dtReport.Rows[i]["PostingOutQty"] = Convert.ToDecimal(dtReport.Rows[i]["PostingOutQty"].ToString()) * -1;
dtReport.Rows[i]["TotalInQty"] = Convert.ToDecimal(dtReport.Rows[i]["TodayInQty"].ToString()) + Convert.ToDecimal(dtReport.Rows[i]["PostingInQty"].ToString());
dtReport.Rows[i]["TotalOutQty"] = Convert.ToDecimal(dtReport.Rows[i]["TodayOutQty"].ToString()) + Convert.ToDecimal(dtReport.Rows[i]["PostingOutQty"].ToString());
dtReport.Rows[i]["RemaindQty"] = Convert.ToDecimal(dtReport.Rows[i]["TotalInQty"].ToString()) - Convert.ToDecimal(dtReport.Rows[i]["TotalOutQty"].ToString());
}
DataTable dtSubReport = new DataTable();
DataTable dtAccount = new DataTable();
dtSubReport.Columns.Add("acc_name");
dtSubReport.Columns.Add("accType");
dtSubReport.Columns.Add("TodayAcc");
dtSubReport.Columns.Add("PosingAcc");
dtSubReport.Columns.Add("TotalAcc");
dtSubReport.Columns.Add("linkR");
dtSubReport.Columns.Add("TodayNetAmount");
dtSubReport.Columns.Add("PosingNetAmount");
dtAccount = cnn.GetDataTable("select acc.acc_name ,'الايرادات' accType, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no like '6110100001%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) TodayAcc, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('01/" + dtpDialyDate.Value.ToString("MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no like '6110100001%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) PosingAcc,'0' TotalAcc,'1' linkR,0 TodayNetAmount, 0 PosingNetAmount " +
" from(" +
" select pkid, acc_name " +
" from accounts acc " +
" where level = 5) acc " +
"");
int iStart = 0;
int iEnd = 0;
decimal dTodayNetAmount = 0;
decimal dPosingNetAmount = 0;
for (int i = 0; i < dtAccount.Rows.Count; i++)
{
if (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) != 0 || Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString()) != 0)
{
dtAccount.Rows[i]["TotalAcc"] = (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString())).ToString();
dtSubReport.Rows.Add();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["acc_name"] = dtAccount.Rows[i]["acc_name"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["accType"] = dtAccount.Rows[i]["accType"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TodayAcc"] = dtAccount.Rows[i]["TodayAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["PosingAcc"] = dtAccount.Rows[i]["PosingAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TotalAcc"] = dtAccount.Rows[i]["TotalAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["linkR"] = dtAccount.Rows[i]["linkR"].ToString();
dTodayNetAmount = dTodayNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString());
dPosingNetAmount = dPosingNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString());
iEnd++;
}
}
for (int i = iStart; i < iEnd; i++)
{
dtSubReport.Rows[i]["TodayNetAmount"] = dTodayNetAmount.ToString();
dtSubReport.Rows[i]["PosingNetAmount"] = dPosingNetAmount.ToString();
}
iStart = iEnd;
dtAccount.Rows.Clear();
dtAccount = cnn.GetDataTable("select acc.acc_name ,'المصروفات' accType, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no like '5%' and a.Acc_no not in ('5110100001','5120100001','5140100001','5130100001') and a.Acc_no not like '52213%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) TodayAcc, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('01/" + dtpDialyDate.Value.ToString("MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no like '5%' and a.Acc_no not in ('5110100001','5120100001','5140100001','5130100001') and a.Acc_no not like '52213%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) PosingAcc,'0' TotalAcc,'1' linkR,0 TodayNetAmount, 0 PosingNetAmount " +
" from(" +
" select pkid, acc_name " +
" from accounts acc " +
" where level = 5) acc " +
"");
dTodayNetAmount = 0;
dPosingNetAmount = 0;
for (int i = 0; i < dtAccount.Rows.Count; i++)
{
if (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) != 0 || Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString()) != 0)
{
string str1 = dtAccount.Rows[i]["TodayAcc"].ToString();
string str2 = dtAccount.Rows[i]["PosingAcc"].ToString();
dtAccount.Rows[i]["TotalAcc"] = (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString())).ToString();
dtSubReport.Rows.Add();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["acc_name"] = dtAccount.Rows[i]["acc_name"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["accType"] = dtAccount.Rows[i]["accType"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TodayAcc"] = dtAccount.Rows[i]["TodayAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["PosingAcc"] = dtAccount.Rows[i]["PosingAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TotalAcc"] = dtAccount.Rows[i]["TotalAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["linkR"] = dtAccount.Rows[i]["linkR"].ToString();
dTodayNetAmount = dTodayNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString());
dPosingNetAmount = dPosingNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString());
iEnd++;
}
}
for (int i = iStart; i < iEnd; i++)
{
dtSubReport.Rows[i]["TodayNetAmount"] = dTodayNetAmount.ToString();
dtSubReport.Rows[i]["PosingNetAmount"] = dPosingNetAmount.ToString();
}
iStart = iEnd;
dtAccount.Rows.Clear();
dtAccount = cnn.GetDataTable("select acc.acc_name ,'أخرى' accType, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no not like '5%' and a.Acc_no not like '6%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) TodayAcc, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('01/" + dtpDialyDate.Value.ToString("MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no not like '5%' and a.Acc_no not like '6%' and a.acc_no not like '4%'" +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) PosingAcc,'0' TotalAcc,'1' linkR,0 TodayNetAmount, 0 PosingNetAmount " +
" from(" +
" select pkid, acc_name " +
" from accounts acc " +
" where level = 5) acc " +
"");
dTodayNetAmount = 0;
dPosingNetAmount = 0;
for (int i = 0; i < dtAccount.Rows.Count; i++)
{
if (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) != 0 || Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString()) != 0)
{
string str1 = dtAccount.Rows[i]["TodayAcc"].ToString();
string str2 = dtAccount.Rows[i]["PosingAcc"].ToString();
dtAccount.Rows[i]["TotalAcc"] = (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString())).ToString();
dtSubReport.Rows.Add();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["acc_name"] = dtAccount.Rows[i]["acc_name"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["accType"] = dtAccount.Rows[i]["accType"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TodayAcc"] = dtAccount.Rows[i]["TodayAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["PosingAcc"] = dtAccount.Rows[i]["PosingAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TotalAcc"] = dtAccount.Rows[i]["TotalAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["linkR"] = dtAccount.Rows[i]["linkR"].ToString();
dTodayNetAmount = dTodayNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString());
dPosingNetAmount = dPosingNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString());
iEnd++;
}
}
for (int i = iStart; i < iEnd; i++)
{
dtSubReport.Rows[i]["TodayNetAmount"] = dTodayNetAmount.ToString();
dtSubReport.Rows[i]["PosingNetAmount"] = dPosingNetAmount.ToString();
}
iStart = iEnd;
cryRepDialyActivity report = new cryRepDialyActivity();
//يجب اسناد التقرير الفرعي قبل التقرير الرئيسي
// ليتم ظهور البيانات
report.Subreports[0].SetDataSource(dtSubReport);
report.SetDataSource(dtReport);
// report.Refresh();
frm.CrystalReportsViewer1.ReportSource = report;
report.SetParameterValue("From", dtpDialyDate.Value.ToString("dd/MM/yyyy"));
// frm.CrystalReportsViewer1.Refresh();
frm.ShowDialog();
}
private void dgProperities_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
if (e.ColumnIndex == 2)
{
lstEditor.DataSource = null;
lstEditor.Items.Clear();
lstMultiEditor.Items.Clear();
if (dgProperities[3, e.RowIndex].Value.ToString() == "Enability")
{
lstEditor.Items.Add("تفعيل");
lstEditor.Items.Add("اغلاق");
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "Visability")
{
lstEditor.Items.Add("اظهار");
lstEditor.Items.Add("اخفاء");
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "Text")
{
return;
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "Free List")
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtFreeList = cnn.GetDataTable("select display_member,value_member from free_list " +
" where list_name = '" + dgProperities[5, e.RowIndex].Value.ToString() + "'");
lstEditor.DataSource = dtFreeList;
lstEditor.ValueMember = "value_member";
lstEditor.DisplayMember = "display_member";
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "Free MultiChoices")
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtFreeList = cnn.GetDataTable("select display_member,value_member from free_list " +
" where list_name = '" + dgProperities[5, e.RowIndex].Value.ToString() + "'");
for (int i = 0; i < dtFreeList.Rows.Count; i++)
{
lstMultiEditor.Items.Add(dtFreeList.Rows[i][0].ToString());
}
DataGridViewCell abc1 = dgProperities.CurrentCell;
Rectangle RECT1 = dgProperities.GetCellDisplayRectangle(abc1.ColumnIndex, abc1.RowIndex, true);
if (dgProperities[2, e.RowIndex].Value.ToString() != "")
{
for (int i = 0; i < lstMultiEditor.Items.Count; i++)
{
lstMultiEditor.SetItemChecked(i, false);
}
string strMult = dgProperities[2, e.RowIndex].Value.ToString();
string iindexx = "";
while (strMult != "")
{
iindexx = (strMult.Substring(0, strMult.IndexOf(";")));
strMult = strMult.Substring(strMult.IndexOf(";") + 1);
for (int i = 0; i < lstMultiEditor.Items.Count; i++)
{
if (lstMultiEditor.Items[i].ToString() == iindexx)
{
lstMultiEditor.SetItemChecked(i, true);
}
}
}
//***************************
}
lstMultiEditor.Tag = e.RowIndex.ToString();
CtlLocation(RECT1, lstMultiEditor);
return;
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "List")
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtListTable = cnn.GetDataTable("select select_statement from LIST_TYPE t " +
" where pkid = '" + dgProperities[4, e.RowIndex].Value.ToString() + "'");
DataTable dtGetList = cnn.GetDataTable(dtListTable.Rows[0][0].ToString());
lstEditor.DataSource = dtGetList;
lstEditor.ValueMember = "pkid";
lstEditor.DisplayMember = "name";
if (dgProperities[5, e.RowIndex].Value.ToString() != "")
{
DataTable dtFreeList = cnn.GetDataTable("select display_member,value_member from free_list " +
" where list_name = '" + dgProperities[5, e.RowIndex].Value.ToString() + "'");
DataRow dr;
for (int i = 0; i < dtFreeList.Rows.Count; i++)
{
dr = dtGetList.NewRow();
dr["name"] = dtFreeList.Rows[i]["display_member"].ToString();
dr["id"] = Convert.ToInt16(dtFreeList.Rows[i]["value_member"].ToString());
dtGetList.Rows.InsertAt(dr, 0);
}
//MyDataTable.Rows.InsertAt(dr, 0);
//dr = MyDataTable.NewRow();
//dr["BRANCHE_ANAME"] = "فرع المستخدم";
//dr["swid"] = glb_function.glb_strUserId;
//MyDataTable.Rows.InsertAt(dr, 0);
}
}
else if (dgProperities[3, e.RowIndex].Value.ToString() == "MultiChoices")
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtListTable = cnn.GetDataTable("select select_statement from LIST_TYPE t " +
" where pkid = '" + dgProperities[4, e.RowIndex].Value.ToString() + "'");
DataTable dtGetList = cnn.GetDataTable(dtListTable.Rows[0][0].ToString());
for (int i = 0; i < dtGetList.Rows.Count; i++)
{
lstMultiEditor.Items.Add(dtGetList.Rows[i][1].ToString());
}
DataGridViewCell abc1 = dgProperities.CurrentCell;
Rectangle RECT1 = dgProperities.GetCellDisplayRectangle(abc1.ColumnIndex, abc1.RowIndex, true);
if (dgProperities[2, e.RowIndex].Value.ToString() != "")
{
//lstMultiEditor
//lstEditor.Text = dgProperities[2, e.RowIndex].Value.ToString();
//***************************
for (int i = 0; i < lstMultiEditor.Items.Count; i++)
{
lstMultiEditor.SetItemChecked(i, false);
}
string strMult = dgProperities[2, e.RowIndex].Value.ToString();
string iindexx = "";
while (strMult != "")
{
iindexx = (strMult.Substring(0, strMult.IndexOf(";")));
strMult = strMult.Substring(strMult.IndexOf(";") + 1);
for (int i = 0; i < lstMultiEditor.Items.Count; i++)
{
if (lstMultiEditor.Items[i].ToString() == iindexx)
{
lstMultiEditor.SetItemChecked(i, true);
}
}
}
//***************************
}
lstMultiEditor.Tag = e.RowIndex.ToString();
CtlLocation(RECT1, lstMultiEditor);
return;
}
else
{
glb_function.MsgBox("قيمة غير معروفة");
txtFormArName.Focus();
return;
}
//dgProperities.RightToLeft = RightToLeft.No;
DataGridViewCell abc = dgProperities.CurrentCell;
Rectangle RECT = dgProperities.GetCellDisplayRectangle(abc.ColumnIndex, abc.RowIndex, true);
//dgProperities.RightToLeft = RightToLeft.Yes;
if (dgProperities[2, e.RowIndex].Value.ToString() != "")
{
lstEditor.Text = dgProperities[2, e.RowIndex].Value.ToString();
}
lstEditor.Tag = e.RowIndex.ToString();
CtlLocation(RECT, lstEditor);
}
}
}
private void GetData(string strPkid)
{
new glb_function().clearItems(this);
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtJournalData = cnn.GetDataTable("SELECT h.Pkid, h.stat, Branch_id, jour_no, trans_name, trans_id, date_format(jour_date,'%d/%m/%Y') jour_date, jour_note,Person, " +
" d.pkid dpkid, d.stat dstat, curr_id,(select c.curr_name from sales.currency c where c.pkid=d.curr_id) curr_name, acc_id, main_value, jour_value, exchange_Rate, jour_details, " +
" a.acc_no, a.acc_name " +
" FROM journal_header h " +
" join journal_details d " +
" join accounts a on(a.pkid = d.acc_id) " +
" on(h.pkid = d.header_id) " +
" where h.pkid = " + strPkid + " order by d.pkid");
bLoad = true;
txtPkid.Text = strPkid;
txtJourNo.Text = dtJournalData.Rows[0]["jour_no"].ToString();
dtpJourDate.SelectedDate = DateTime.ParseExact(dtJournalData.Rows[0]["jour_date"].ToString(), "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
txtHeaderNote.Text = dtJournalData.Rows[0]["jour_note"].ToString();
txtCreditTotal.Text = "0";
txtDeptTotal.Text = "0";
for (int i = 0; i < dtJournalData.Rows.Count; i++)
{
clsJourDetail newRow = new clsJourDetail();
if (Convert.ToDouble(dtJournalData.Rows[i]["main_value"].ToString()) > 0)
{
newRow.clmDept = dtJournalData.Rows[i]["main_value"].ToString();
newRow.clmCurrValue = dtJournalData.Rows[i]["jour_value"].ToString();
newRow.clmCredit = "0";
}
else
{
newRow.clmCredit = (Convert.ToDouble(dtJournalData.Rows[i]["main_value"].ToString()) * -1).ToString();
newRow.clmCurrValue = (Convert.ToDouble(dtJournalData.Rows[i]["jour_value"].ToString()) * -1).ToString();
newRow.clmDept = "0";
}
newRow.clmPKid = dtJournalData.Rows[i]["dpkid"].ToString();
newRow.clmCurrencyId = dtJournalData.Rows[i]["curr_id"].ToString();
newRow.clmCurrencyName = dtJournalData.Rows[i]["curr_name"].ToString();
newRow.clmAccId = dtJournalData.Rows[i]["acc_id"].ToString();
newRow.clmAccNo = dtJournalData.Rows[i]["acc_no"].ToString();
newRow.clmAccName = dtJournalData.Rows[i]["acc_name"].ToString();
newRow.clmJourNote = dtJournalData.Rows[i]["jour_details"].ToString();
newRow.clmExchangeRate = dtJournalData.Rows[i]["exchange_Rate"].ToString();
dgvJourDetails.Items.Add(newRow);
txtCreditTotal.Text = (Convert.ToDouble(txtCreditTotal.Text.Trim()) + Convert.ToDouble(newRow.clmCredit)).ToString();
txtDeptTotal.Text = (Convert.ToDouble(txtDeptTotal.Text.Trim()) + Convert.ToDouble(newRow.clmDept)).ToString();
}
if (txtCreditTotal.Text.Trim() == txtDeptTotal.Text.Trim())
{
txtDeptTotal.Background = Brushes.LawnGreen;
txtCreditTotal.Background = Brushes.LawnGreen;
}
else
{
txtDeptTotal.Background = Brushes.Red;
txtCreditTotal.Background = Brushes.Red;
}
if (UserTemplate.HasPrivilege("btnUpdate"))
{
btnUpdate.IsEnabled = true;
}
btnSave.IsEnabled = false;
bLoad = false;
}
private void btnUpdate_Click(object sender, RoutedEventArgs e)
{
if (!CheckEntries())
{
return;
}
ConnectionToMySQL cnn = new ConnectionToMySQL();
int icheck = 0;
icheck = cnn.TranDataToDB("update journal_header set " +
" created_user="******",jour_date=str_to_date('" + dtpJourDate.SelectedDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",jour_note='" + txtHeaderNote.Text.Trim() + "'" +
" where pkid=" + txtPkid.Text);
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء تعديل البيانات الأساسية");
return;
}
icheck = cnn.TranDataToDB("delete from journal_details where header_id= " + txtPkid.Text);
if (icheck <= 0)
{
cnn.glb_commitTransaction();
glb_function.MsgBox("حدث خطأ اثناء حذف البيانات السابقة");
return;
}
for (int i = 0; i < dgvJourDetails.Items.Count; i++)
{
double dMainValue = 0;
double dCurrValue = 0;
if (Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmDept.DisplayIndex, i)) > 0)
{
dMainValue = Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmDept.DisplayIndex, i));
dCurrValue = Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmCurrValue.DisplayIndex, i));
}
else
{
dMainValue = Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmCredit.DisplayIndex, i)) * -1;
dCurrValue = Convert.ToDouble(glb_function.GetCellValue(ref dgvJourDetails, clmCurrValue.DisplayIndex, i)) * -1;
}
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + glb_function.GetCellValue(ref dgvJourDetails, clmCurrencyId.DisplayIndex, i) +
"," + glb_function.GetCellValue(ref dgvJourDetails, clmAccId.DisplayIndex, i) +
"," + dMainValue +
"," + dCurrValue +
"," + glb_function.GetCellValue(ref dgvJourDetails, clmExchangeRate.DisplayIndex, i) +
",'" + glb_function.GetCellValue(ref dgvJourDetails, clmJourNote.DisplayIndex, i) + "'" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء تعديل التفاصيل");
return;
}
}
cnn.glb_commitTransaction();
GetData(txtPkid.Text);
if (glb_function.MsgBox("تمت عملية التعديل بنجاح" + "\n" + "هل تريد طباعة سند القيد؟", "", true) == false)
{
return;
}
}
private void AccSheetSummary()
{
if (lstAccNo.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار الحساب");
return;
}
cryRepAccSheetSummary report = new cryRepAccSheetSummary();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
TextObject txtArTitel = (TextObject)report.ReportDefinition.ReportObjects["txtArTitel"];
txtArTitel.Text = glb_function.strArabicTitel;
TextObject txtEnTitel = (TextObject)report.ReportDefinition.ReportObjects["txtEnTitel"];
txtEnTitel.Text = glb_function.strEnglishTitel;
double dBalance = 0;
string strDate = "";
string strStat = "";
if (ckbPosting.Checked == true)
{
strStat = " and h.stat in ('فعال', 'مرحل')";
}
else
{
strStat = " and h.stat in ( 'مرحل')";
}
strStat = strStat + " and ifnull(d.profitCenter,'') like '%" + lstCostCent.Text.Trim() + "%'";
if (ckbSelectDate.Checked == true)
{
strDate = " and jour_date between str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpTo.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
dtReport = cnn.GetDataTable("select ifnull(sum(main_value) ,0) balance " +
" from journal_header h " +
" join journal_details d on (h.pkid = d.header_id) " +
" where d.acc_id=" + lstAccNo.SelectedValue.ToString() + " and h.created_date < str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y') " + strStat);
dBalance = Convert.ToDouble(dtReport.Rows[0][0].ToString());
}
else
{
dBalance = 0;
}
dtReport.Clear();
dtReport = cnn.GetDataTable("select h.pkid,h.jour_no,h.trans_no,h.trans_name,date_format(h.jour_date,'%d/%m/%Y') jour_date,h.jour_note, " +
" if (d.main_value > 0,d.main_value,0) Dept,if (d.main_value < 0,d.main_value * -1,0) Credit,d.jour_details,0 Balance, 'مدين' BalStat" +
" from journal_header h " +
" join journal_details d on(h.pkid= d.header_id)" +
" where d.acc_id=" + lstAccNo.SelectedValue.ToString() + strStat + strDate + " order by h.jour_date ,d.pkid ");
for (int i = 0; i < dtReport.Rows.Count; i++)
{
dBalance = dBalance + Convert.ToDouble(dtReport.Rows[i]["Dept"].ToString()) - Convert.ToDouble(dtReport.Rows[i]["Credit"].ToString());
if (dBalance < 0)
{
dtReport.Rows[i]["Balance"] = dBalance * -1;
dtReport.Rows[i]["BalStat"] = "دائن";
}
else
{
dtReport.Rows[i]["Balance"] = dBalance;
dtReport.Rows[i]["BalStat"] = "مدين";
}
// dtReport.Rows[i]["Balance"]
}
report.SetDataSource(dtReport);
report.SetParameterValue("From", (ckbSelectDate.Checked == false ? "" : dtpFrom.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("To", (ckbSelectDate.Checked == false ? DateTime.Now.ToString("dd/MM/yyyy") : dtpTo.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("AccNo", lstAccNo.Text);
report.SetParameterValue("AccName", lstAccName.Text);
// report.SetParameterValue("strValueAlpha", new ConvertNumbersToArabicAlphabet(txtCreditTotal.Text).GetNumberAr());
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void lstTemplet_SelectedIndexChanged(object sender, EventArgs e)
{
{
string strStat = "select h.pkid,h.form_eng_name,h.form_type_en ,d.control_type,d.en_name,d.real_value,d.ar_name,multi_form_for_user " +
" from templet_header h,templet_details d" +
" where h.pkid = d.header_id " +
" and h.pkid = " + strTempletId;
ConnectionToMySQL cnn = new ConnectionToMySQL();
dtPri = cnn.GetDataTable(strStat);
//if (dtPri.Rows[0]["multi_form_for_user"].ToString() == "1")
//{
// lstTemplet.Visible = true;
//}
for (int i = 0; i < dtPri.Rows.Count; i++)
{
if (dtPri.Rows[i]["control_type"].ToString() == "Visability")
{
if (dtPri.Rows[i]["real_value"].ToString() == "1")
{
Visual ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
((Control )ctr).Visibility = Visibility.Visible;
}
}
else
{
Visual ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
((Control)ctr).Visibility = Visibility.Hidden;
}
}
}
else if (dtPri.Rows[i]["control_type"].ToString() == "Enability")
{
if (dtPri.Rows[i]["real_value"].ToString() == "1")
{
Visual ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
((Control)ctr).IsEnabled = true;
}
//string strss = dtPri.Rows[i]["en_name"].ToString();
//this.Controls[dtPri.Rows[i]["en_name"].ToString()].Enabled = true;
}
else
{
Visual ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
((Control)ctr).IsEnabled = false;
}
}
}
else if (dtPri.Rows[i]["control_type"].ToString() == "List" || dtPri.Rows[i]["control_type"].ToString() == "Text" || dtPri.Rows[i]["control_type"].ToString() == "MultiChoices" || dtPri.Rows[i]["control_type"].ToString() == "Free MultiChoices" || dtPri.Rows[i]["control_type"].ToString() == "Free List")
{
Visual ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
//if(ctr.GetType()==typeof(ComboBox) )
// ((ComboBox)ctr).Text = dtPri.Rows[i]["real_value"].ToString();
//if (ctr.GetType() == typeof(TextBox))
((TextBox)ctr).Text = dtPri.Rows[i]["real_value"].ToString();
}
//}
}
}
}
}
private void btnGetCloseingEntry_Click(object sender, EventArgs e)
{
dgvJourDetails.Rows.Clear();
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtProfitExp = cnn.GetDataTable("SELECT acc_id,a.Acc_no,a.acc_name, sum(main_value),if (sum(main_value) > 0,sum(main_value),0) Dept," +
" if (sum(main_value) < 0,sum(main_value) * -1,0) Credit" +
" FROM journal_details d" +
" join accounts a on(d.acc_id = a.pkid)" +
" where acc_id like '5%'" +
" group by acc_id" +
" having sum(main_value) != 0");
for (int i = 0; i < dtProfitExp.Rows.Count; i++)
{
dgvJourDetails.Rows.Add();
dgvJourDetails[clmAccId.Index, i].Value = dtProfitExp.Rows[i]["acc_id"].ToString();
dgvJourDetails[clmAccNo.Index, i].Value = dtProfitExp.Rows[i]["Acc_no"].ToString();
dgvJourDetails[clmAccName.Index, i].Value = dtProfitExp.Rows[i]["acc_name"].ToString();
dgvJourDetails[clmCredit.Index, i].Value = Convert.ToDecimal(dtProfitExp.Rows[i]["Dept"].ToString()).ToString("###,###,###,##0.##");
dgvJourDetails[clmDept.Index, i].Value = "0";
dgvJourDetails[clmJourNote.Index, i].Value = "قيد الاقفال لسنة " + glb_function.glb_Year;
}
dtProfitExp.Rows.Clear();
dtProfitExp = cnn.GetDataTable("SELECT acc_id,a.Acc_no,a.acc_name, sum(main_value),if (sum(main_value) > 0,sum(main_value),0) Dept," +
" if (sum(main_value) < 0,sum(main_value) * -1,0) Credit" +
" FROM journal_details d" +
" join accounts a on(d.acc_id = a.pkid)" +
" where acc_id like '6%'" +
" group by acc_id" +
" having sum(main_value) != 0");
for (int i = 0; i < dtProfitExp.Rows.Count; i++)
{
dgvJourDetails.Rows.Add();
dgvJourDetails[clmAccId.Index, dgvJourDetails.Rows.Count - 1].Value = dtProfitExp.Rows[i]["acc_id"].ToString();
dgvJourDetails[clmAccNo.Index, dgvJourDetails.Rows.Count - 1].Value = dtProfitExp.Rows[i]["Acc_no"].ToString();
dgvJourDetails[clmAccName.Index, dgvJourDetails.Rows.Count - 1].Value = dtProfitExp.Rows[i]["acc_name"].ToString();
dgvJourDetails[clmDept.Index, dgvJourDetails.Rows.Count - 1].Value = Convert.ToDecimal(dtProfitExp.Rows[i]["Credit"].ToString()).ToString("###,###,###,##0.##");
dgvJourDetails[clmCredit.Index, dgvJourDetails.Rows.Count - 1].Value = "0";
dgvJourDetails[clmJourNote.Index, dgvJourDetails.Rows.Count - 1].Value = "قيد الاقفال لسنة " + glb_function.glb_Year;
}
GetTotal();
dgvJourDetails.Rows.Add();
dgvJourDetails[clmAccId.Index, dgvJourDetails.Rows.Count - 1].Value = lstAccNo.SelectedValue.ToString();
dgvJourDetails[clmAccNo.Index, dgvJourDetails.Rows.Count - 1].Value = lstAccNo.Text;
dgvJourDetails[clmAccName.Index, dgvJourDetails.Rows.Count - 1].Value = lstAccName.Text;
dgvJourDetails[clmDept.Index, dgvJourDetails.Rows.Count - 1].Value = txtCreditTotal.Text.Trim();
dgvJourDetails[clmCredit.Index, dgvJourDetails.Rows.Count - 1].Value = "0";
dgvJourDetails[clmJourNote.Index, dgvJourDetails.Rows.Count - 1].Value = "قيد الاقفال لسنة " + glb_function.glb_Year;
dgvJourDetails.Rows.Add();
dgvJourDetails[clmAccId.Index, dgvJourDetails.Rows.Count - 1].Value = lstAccNo.SelectedValue.ToString();
dgvJourDetails[clmAccNo.Index, dgvJourDetails.Rows.Count - 1].Value = lstAccNo.Text;
dgvJourDetails[clmAccName.Index, dgvJourDetails.Rows.Count - 1].Value = lstAccName.Text;
dgvJourDetails[clmCredit.Index, dgvJourDetails.Rows.Count - 1].Value = txtDeptTotal.Text.Trim();
dgvJourDetails[clmDept.Index, dgvJourDetails.Rows.Count - 1].Value = "0";
dgvJourDetails[clmJourNote.Index, dgvJourDetails.Rows.Count - 1].Value = "قيد الاقفال لسنة " + glb_function.glb_Year;
GetTotal();
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!CheckEntries())
{
return;
}
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtBiginingEntry = cnn.GetDataTable("select pkid,stat from journal_header where trans_name='قيد افتتاحي' and date_format(jour_date,'%Y')='" + dtpJourDate.Value.ToString("yyyy") + "' ");
int icheck = 0;
if (dtBiginingEntry == null || dtBiginingEntry.Rows.Count <= 0)
{
DataTable dtKeys = cnn.GetDataTable("select (select ifnull(max(trans_id),0)+1 FROM journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " and trans_name='قيد افتتاحي') IssueNo,(select ifnull(max(pkid),0)+1 from journal_header) pkid,(select ifnull(max( convert( substring(jour_no,instr(jour_no,'-')+1),signed)),0)+1 FROM journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " ) JourNo");
txtPkid.Text = dtKeys.Rows[0]["pkid"].ToString();
string strIssueNo = dtKeys.Rows[0]["IssueNo"].ToString();
txtJourNo.Text = glb_function.glb_strBranchPkid + "-" + dtKeys.Rows[0]["JourNo"].ToString();
icheck = cnn.TranDataToDB("insert into journal_header values (" + txtPkid.Text +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + glb_function.glb_strBranchPkid +
",'" + txtJourNo.Text.Trim() + "'" +
",'قيد افتتاحي'" +
"," + strIssueNo +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",'" + txtHeaderNote.Text.Trim() + "'" +
",null" +
",'" + glb_function.glb_strBranchPkid + "-" + strIssueNo + "'" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء حفظ البيانات الأساسية");
return;
}
}
else
{
txtPkid.Text = dtBiginingEntry.Rows[0]["pkid"].ToString();
}
for (int i = 0; i < dgvJourDetails.Rows.Count; i++)
{
if (dgvJourDetails[clmAccId.Index, i].Value == null || dgvJourDetails[clmAccId.Index, i].Value.ToString() == "")
{
continue;
}
decimal dMainValue = 0;
decimal dCurrValue = 0;
if (Convert.ToDecimal(dgvJourDetails[clmDept.Index, i].Value) > 0)
{
dMainValue = Convert.ToDecimal(dgvJourDetails[clmDept.Index, i].Value);
dCurrValue = dMainValue;
}
else
{
dMainValue = Convert.ToDecimal(dgvJourDetails[clmCredit.Index, i].Value) * -1;
dCurrValue = dMainValue;
}
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + glb_function.glb_iMainCurrId +
"," + dgvJourDetails[clmAccId.Index, i].Value +
"," + dMainValue +
"," + dCurrValue +
",1" +
",'" + dgvJourDetails[clmJourNote.Index, i].Value + "'" +
",'" + dgvJourDetails[clmProfitCenter.Index, i].Value + "'" +
",'" + dgvJourDetails[clmProfitCenter1.Index, i].Value + "'" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
}
cnn.glb_commitTransaction();
GetData(txtPkid.Text);
glb_function.MsgBox("تمت عملية الحفظ بنجاح");
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (txtUserName.Text.Trim() == "")
{
glb_function.MsgBox("الرجاء ادخال اسم المستخدم");
txtUserName.Focus();
return;
}
if (txtUserLogin.Text.Trim() == "")
{
glb_function.MsgBox("الرجاء ادخال رمز الدخول");
txtUserLogin.Focus();
return;
}
if (lstBranches.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار الفرع");
lstBranches.Focus();
return;
}
ConnectionToMySQL cnnSave = new ConnectionToMySQL();
DataTable dt = cnnSave.GetDataTable("(select ifnull(max(b.pkid),0)+1 from users b)");
txtPkid.Text = dt.Rows[0][0].ToString();
int icheck = cnnSave.TranDataToDB("insert into users " +
" values(" + txtPkid.Text + ",'فعال',SYSDATE() ," + glb_function.glb_strUserId +
",'" + txtUserLogin.Text.Trim() + "','" + new glb_function().Encrypt(txtUserLogin.Text.Trim(), true) + "','" + txtUserName.Text.Trim() +
"','" + new glb_function().Encrypt(txtPassword.Password.Trim(), true) + "','" + txtNote.Text.Trim() + "'," + lstBranches.SelectedValue.ToString() + ")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات المستخدم");
return;
}
icheck = cnnSave.TranDataToDB("delete from USER_TEMPLET where user_id=" + txtPkid.Text);
if (icheck < 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات المستخدم");
return;
}
GetTempletToSave();
while (strSelectedTemp != "")
{
string strTemp = strSelectedTemp.Substring(0, strSelectedTemp.IndexOf(';'));
icheck = cnnSave.TranDataToDB("insert into USER_TEMPLET values ((select ifnull(max(b.pkid),0)+1 from USER_TEMPLET b), " + txtPkid.Text + "," + strTemp + ")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات المستخدم");
return;
}
strSelectedTemp = strSelectedTemp.Substring(strSelectedTemp.IndexOf(';') + 1);
}
cnnSave.glb_commitTransaction();
glb_function.MsgBox("تمت العملية بنجاح");
GetData(txtPkid.Text);
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
foreach (RibbonTab tab in FindLogicalChildren <RibbonTab>(this.RibbonWin))
{
tab.Visibility = Visibility.Hidden;
foreach (RibbonButton button in FindLogicalChildren <RibbonButton>(tab))
{
button.Visibility = Visibility.Hidden;
button.MinWidth = 100;
if (button.CommandParameter != null)
{
button.Label = button.CommandParameter.ToString();
}
if (button.Content != null)
{
button.LargeImageSource = new BitmapImage(new Uri(@"" + (button.Content.ToString()), UriKind.Relative));
}
button.Margin = new Thickness(5, 0, 0, 0);
}
}
ConnectionToMySQL myconn = new ConnectionToMySQL();
// DataTable MyDataTable;
new frmLogin().ShowDialog();
if (glb_function.glb_strUserId == "")
{
return;
}
// glb_function.glb_strUserName = "******";
if (glb_function.glb_strUserName == "وسيم الأغبري")
{
foreach (RibbonTab tab in FindLogicalChildren <RibbonTab>(this.RibbonWin))
{
tab.Visibility = Visibility.Visible;
foreach (RibbonButton button in FindLogicalChildren <RibbonButton>(tab))
{
button.Visibility = Visibility.Visible;
button.MinWidth = 100;
if (button.CommandParameter != null)
{
button.Label = button.CommandParameter.ToString();
}
if (button.Content != null)
{
button.LargeImageSource = new BitmapImage(new Uri(@"" + (button.Content.ToString()), UriKind.Relative));
}
button.Margin = new Thickness(5, 0, 0, 0);
}
}
tabFile.IsSelected = true;
tabFile.UpdateLayout();
RibbonWin.UpdateLayout();
return;
}
dtUserForm = myconn.GetDataTable("select u.pkid,m.form_arabic_name,m.form_eng_name,m.form_type_en from templet_header m,user_templet u " +
" where m.pkid = u.templet_id and u.user_id= " + glb_function.glb_strUserId);
CreateToolIcons(this);
}
private void GetData(string strInvoiceId)
{
dgvExpensses.Rows.Clear();
dgvItems.Rows.Clear();
nmbExpTotal.Value = 0;
nmbItemTotal.Value = 0;
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtPurchares = cnn.GetDataTable("SELECT pkid,invoice_no,warehosue_id,acc_id,pi_note " +
" FROM pi_header " +
" where pkid=" + strInvoiceId);
txtPkid.Text = dtPurchares.Rows[0]["pkid"].ToString();
txtInvoiceNo.Text = dtPurchares.Rows[0]["invoice_no"].ToString();
lstWarehouse.SelectedValue = dtPurchares.Rows[0]["warehosue_id"].ToString();
lstAccNo.SelectedValue = dtPurchares.Rows[0]["acc_id"].ToString();
lstAccName.SelectedValue = dtPurchares.Rows[0]["acc_id"].ToString();
txtInvoiceNote.Text = dtPurchares.Rows[0]["pi_note"].ToString();
dtPurchares.Clear();
dtPurchares = cnn.GetDataTable("select d.pkid,d.qty,d.unit_price,d.unit_cost,i.itemno,i.itemname " +
" from pi_details d " +
" join items i on (i.pkid = d.item_id) " +
" where d.header_id = " + strInvoiceId);
for (int i = 0; i < dtPurchares.Rows.Count; i++)
{
dgvItems.Rows.Add();
dgvItems[clmItemPKid.Index, i].Value = dtPurchares.Rows[i]["pkid"].ToString();
dgvItems[clmItemNo.Index, i].Value = dtPurchares.Rows[i]["itemno"].ToString();
dgvItems[clmItemName.Index, i].Value = dtPurchares.Rows[i]["itemname"].ToString();
dgvItems[clmItemQty.Index, i].Value = dtPurchares.Rows[i]["qty"].ToString();
dgvItems[clmItemPrice.Index, i].Value = dtPurchares.Rows[i]["unit_price"].ToString();
nmbItemTotal.Value += Convert.ToDecimal(dtPurchares.Rows[i]["qty"].ToString()) * Convert.ToDecimal(dtPurchares.Rows[i]["unit_price"].ToString());
}
dtPurchares.Clear();
dtPurchares = cnn.GetDataTable("SELECT pie.pkid,expenss_name,pie.value,pie.acc_id,a.Acc_no,a.acc_name " +
" FROM pi_expensses pie " +
" join accounts a on(a.pkid = pie.acc_id) " +
" where pie.header_id = " + strInvoiceId);
for (int i = 0; i < dtPurchares.Rows.Count; i++)
{
dgvExpensses.Rows.Add();
dgvExpensses[clmAccId.Index, i].Value = dtPurchares.Rows[i]["acc_id"].ToString();
dgvExpensses[clmAccNo.Index, i].Value = dtPurchares.Rows[i]["Acc_no"].ToString();
dgvExpensses[clmAccName.Index, i].Value = dtPurchares.Rows[i]["acc_name"].ToString();
dgvExpensses[clmExpName.Index, i].Value = dtPurchares.Rows[i]["expenss_name"].ToString();
dgvExpensses[clmExpValue.Index, i].Value = dtPurchares.Rows[i]["value"].ToString();
nmbExpTotal.Value += Convert.ToDecimal(dtPurchares.Rows[i]["value"].ToString());
}
// if (UserTemplate.HasPrivilege("btnPrint"))
btnPrint.Enabled = true;
btnSave.Enabled = false;
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!CheckEntries())
{
return;
}
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtSales = cnn.GetDataTable("select(select ifnull(max(convert(substring(invoice_no, instr(invoice_no, '-') + 1), signed)), 0) + 1 FROM invoice_header where Branch_id = " + glb_function.glb_strBranchPkid + " ) Invoice_no, (select ifnull(max(pkid), 0) + 1 from invoice_header) pkid");
txtPkid.Text = dtSales.Rows[0]["pkid"].ToString();
txtInvoiceNo.Text = glb_function.glb_strBranchPkid + "-" + dtSales.Rows[0]["Invoice_no"].ToString();
int icheck = cnn.TranDataToDB("insert into invoice_header " +
" values(" + txtPkid.Text + "" +
",'فعال'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
"," + glb_function.glb_strUserId +
",'" + txtInvoiceNo.Text.Trim() + "'" +
"," + lstWareHouse.SelectedValue.ToString() +
"," + txtPumpEmpId.Text.Trim() +
"," + lstPumps.SelectedValue.ToString() +
"," + txtEmpId.Text.Trim() +
"," + nmbPreviousCounter.Value.ToString() +
"," + nmbCurrentCounter.Value.ToString() +
",'" + txtInvoiceNote.Text.Trim() + "'" +
"," + txtItemPkid.Text.Trim() +
"," + nmbRequiredQty.Value.ToString() +
"," + nmbUnitSellingPrice.Value.ToString() +
"," + nmbTotalSellingPrice.Value.ToString() +
"," + glb_function.glb_strBranchPkid +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات الفاتورة");
return;
}
icheck = cnn.TranDataToDB("update pumps set PumpCounter=" + nmbCurrentCounter.Value + " ,stat='متاحة' where pkid=" + lstPumps.SelectedValue.ToString());
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات الفاتورة ");
return;
}
icheck = cnn.TranDataToDB("update pump_emp set stat='مقفل' where pkid=" + txtPumpEmpId.Text);
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات الفاتورة ");
return;
}
//خفض الكمية في المخزون
icheck = cnn.TranDataToDB("insert into item_trans values ((select ifnull(max(b.pkid),0)+1 from item_trans b)" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtItemPkid.Text +
"," + lstWareHouse.SelectedValue.ToString() +
",-" + nmbRequiredQty.Value.ToString() +
"," + txtPkid.Text +
",'فاتورة مبيعات'" +
",''" +
",'" + txtInvoiceNo.Text.Trim() + "'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات حركة الاصناف");
return;
}
#region AccountEntry
//القيد المحاسبي *********************
//حفظ بيانات الراس
DataTable dtKeys = cnn.GetDataTable("select (select ifnull(max( convert( substring(jour_no,instr(jour_no,'-')+1),signed)),0)+1 FROM journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " and trans_name='فاتورة بيع') IssueNo,(select ifnull(max(pkid),0)+1 from journal_header) pkid");
string strJPkid = dtKeys.Rows[0]["pkid"].ToString();
// string strIssueNo = dtKeys.Rows[0]["IssueNo"].ToString();
string strJourNo = glb_function.glb_strBranchPkid + "-" + strJPkid;
icheck = 0;
icheck = cnn.TranDataToDB("insert into journal_header values (" + strJPkid +
",'فعال'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
"," + glb_function.glb_strUserId +
"," + glb_function.glb_strBranchPkid +
",'" + strJourNo.Trim() + "'" +
",'فاتورة بيع'" +
"," + txtPkid.Text +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",'قيدت من فاتورة المبيعات'" +
",''" +
",'" + txtInvoiceNo.Text.Trim() + "'" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ البيانات الأساسية");
return;
}
#region FirstEntry
//القيد الاول
// الى حـ المخزون
// من حـ تكلفة البضاعة المباعة
//حفظ بيانات الدائن
//يكون المخزون دائن في فاتورة المبيعات ويميزه انه اصغر من الصفر
double dMainValue = (double)(nmbRequiredQty.Value * nmbUnitCost.Value * -1);
double dCurrValue = dMainValue;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + strInventoryAccId +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
//حفظ بيانات المدين
//
dMainValue = dMainValue * -1; //تضرب في سالب لتحويلها لموجب
dCurrValue = dMainValue;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + strCostGoodSoldAccId +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
#endregion
#region SecondEntry
// القيد الثاني
// من حـ العميل او العامل او الصندوق
// الى حـ المبيعات
//حفظ بيانات الدائن
//تكون المبيعات دائن في فاتورة المبيعات ويميزه انه اصغر من الصفر
double dMainValue2 = (double)(nmbTotalSellingPrice.Value * -1);
double dCurrValue2 = dMainValue2;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + strSalesAccId +
"," + dMainValue2 +
"," + dCurrValue2 +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
//حفظ بيانات المدين
//
dMainValue2 = dMainValue2 * -1; //تضرب في سالب لتحويلها لموجب
dCurrValue2 = dMainValue2;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + txtEmpAccId.Text +
"," + dMainValue2 +
"," + dCurrValue2 +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
#endregion //القيد الثاني للمبيعات
#endregion // قيد الحسابات بالكامل
cnn.glb_commitTransaction();
glb_function.MsgBox("تمت العملية بنجاح");
GetData(txtPkid.Text);
btnPrint.Focus();
}
private void button1_Click(object sender, EventArgs e)
{
if (!CheckEntries())
{
return;
}
glb_function.glb_Year = nmbYear.Value.ToString();
if (lstBranches.SelectedValue.ToString() == "0")
{
//glb_function.strDbCnnString = "server=localhost;port=3306;charset=utf8;database=ammar" + nmbYear.Value.ToString() + ";userid=root;password=ammar7700;SslMode=none;AllowPublicKeyRetrieval=True";
glb_function.strDbCnnString = "server=192.168.1.7;port=3306;charset=utf8;database=headoffice" + nmbYear.Value.ToString() + ";userid=root;password=alforat#Wasim5241;SslMode=none;AllowPublicKeyRetrieval=True";
// glb_function.strDbCnnString = "server=localhost;port=3300;charset=utf8;database=headoffice" + nmbYear.Value.ToString() + ";userid=root;password=bigboss;SslMode=none;AllowPublicKeyRetrieval=True";
glb_function.glb_DB_Name = "headoffice";
glb_function.strArabicTitel = "الأدارة العامة" + "\n" + "اليمن - صنعاء - شارع الرقاص" + "\n" + "رقم التلفون/ 213569 -1-00967";
glb_function.strEnglishTitel = "Head Office" + "\n" + "Yemen - Sana'a - Alrkaas St" + "\n" + "Tel No. : 00967-1-213569";
}
else if (lstBranches.SelectedValue.ToString() == "1")
{
// glb_function.strDbCnnString = "server=localhost;port=3306;charset=utf8;database=ammar" + nmbYear.Value.ToString() + ";userid=root;password=ammar7700;SslMode=none;AllowPublicKeyRetrieval=True";
glb_function.strDbCnnString = "server=192.168.1.7;port=3306;charset=utf8;database=alobur" + nmbYear.Value.ToString() + ";userid=root;password=alforat#Wasim5241;SslMode=none;AllowPublicKeyRetrieval=True";
// glb_function.strDbCnnString = "server=localhost;port=3300;charset=utf8;database=alobur"+nmbYear.Value.ToString()+ ";userid=root;password=bigboss;SslMode=none;AllowPublicKeyRetrieval=True";
glb_function.glb_DB_Name = "alobur";
glb_function.strArabicTitel = "محطة العبور للمحروقات" + "\n" + "اليمن - صنعاء - شارع الرقاص" + "\n" + "رقم التلفون/ 213569 -1-00967";
glb_function.strEnglishTitel = "Al-Obur Petral Station" + "\n" + "Yemen - Sana'a - Alrkaas St" + "\n" + "Tel No. : 00967-1-213569";
}
else if (lstBranches.SelectedValue.ToString() == "100")
{
// glb_function.strDbCnnString = "server=localhost;port=3306;charset=utf8;database=ammar" + nmbYear.Value.ToString() + ";userid=root;password=ammar7700;SslMode=none;AllowPublicKeyRetrieval=True";
// glb_function.strDbCnnString = "server=192.168.1.7;port=3306;charset=utf8;database=ammar"+nmbYear.Value.ToString()+";userid=root;password=alforat#Wasim5241;SslMode=none;AllowPublicKeyRetrieval=True";
glb_function.strDbCnnString = "server=localhost;port=3306;charset=utf8;database=ammar" + nmbYear.Value.ToString() + ";userid=root;password=root;SslMode=none;AllowPublicKeyRetrieval=True";
glb_function.glb_DB_Name = "ammar";
glb_function.strArabicTitel = "الأدارة العامة" + "\n" + "اليمن - صنعاء - شارع الرقاص" + "\n" + "رقم التلفون/ 213569 -1-00967";
glb_function.strEnglishTitel = "Head Office" + "\n" + "Yemen - Sana'a - Alrkaas St" + "\n" + "Tel No. : 00967-1-213569";
}
ConnectionToMySQL.glb_cnn = new MySqlConnection(glb_function.strDbCnnString);
ConnectionToMySQL cnn = new ConnectionToMySQL();
System.Data.DataTable MyDataTable;
MyDataTable = cnn.GetDataTable("SELECT pkid,UserLoginName,UserFullName,password,notes,branch_id FROM users Where userLoginEncry = '" + glb_function.Encrypt(txtUsername.Text.Trim(), true) + "' And Password = '******'");
if (MyDataTable != null && MyDataTable.Rows.Count != 0)
{
glb_function.glb_strUserName = MyDataTable.Rows[0]["UserFullName"].ToString();
glb_function.glb_strUserId = MyDataTable.Rows[0]["pkid"].ToString();
glb_function.glb_strBranchPkid = MyDataTable.Rows[0]["branch_id"].ToString();
glb_function.glb_strBranchName = lstBranches.Text;
if (glb_function.glb_strUserId != "1")
{
MyDataTable = cnn.GetDataTable("SELECT pkid currid,curr_decimal FROM currency where ismaincurr='1' ");
glb_function.glb_iMainCurrId = MyDataTable.Rows[0]["currid"].ToString();
glb_function.glb_iMainCurrDecimal = Convert.ToInt16(MyDataTable.Rows[0]["curr_decimal"].ToString());
}
this.Close();
}
else
{
// new glb_function().MsgBox("خطأ في اسم المستخدم او كلمة السر!", "تسجيل الدخول");
glb_function.MsgBox("خطأ في اسم المستخدم او كلمة السر!", "تسجيل الدخول");
txtPassword.Focus();
}
}
private void lstTemplet_SelectedIndexChanged(object sender, EventArgs e)
{
//if (lstTemplet.SelectedValue == null)
// return;
// if (lstTemplet.SelectedValue.ToString() != "System.Data.DataRowView" && lstTemplet.SelectedValue.ToString() != "")
{
//string strStat = "select h.pkid,h.form_eng_name,h.form_type_en ,d.control_type,d.en_name,d.real_value,d.ar_name,multi_form_for_user " +
// " from templet_header h,templet_details d" +
// " where h.pkid = d.header_id " +
// " and h.pkid = " + lstTemplet.SelectedValue.ToString();
string strStat = "select h.pkid,h.form_eng_name,h.form_type_en ,d.control_type,d.en_name,d.real_value,d.ar_name,multi_form_for_user " +
" from templet_header h,templet_details d" +
" where h.pkid = d.header_id " +
" and h.pkid = " + strTempletId;
ConnectionToMySQL cnn = new ConnectionToMySQL();
dtPri = cnn.GetDataTable(strStat);
//if (dtPri.Rows[0]["multi_form_for_user"].ToString() == "1")
//{
// lstTemplet.Visible = true;
//}
for (int i = 0; i < dtPri.Rows.Count; i++)
{
if (dtPri.Rows[i]["control_type"].ToString() == "Visability")
{
if (dtPri.Rows[i]["real_value"].ToString() == "1")
{
Control ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
ctr.Visible = true;
}
}
else
{
Control ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
ctr.Visible = false;
}
}
}
else if (dtPri.Rows[i]["control_type"].ToString() == "Enability")
{
if (dtPri.Rows[i]["real_value"].ToString() == "1")
{
Control ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
ctr.Enabled = true;
}
//string strss = dtPri.Rows[i]["en_name"].ToString();
//this.Controls[dtPri.Rows[i]["en_name"].ToString()].Enabled = true;
}
else
{
Control ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
ctr.Enabled = false;
}
}
}
else if (dtPri.Rows[i]["control_type"].ToString() == "List" || dtPri.Rows[i]["control_type"].ToString() == "Text" || dtPri.Rows[i]["control_type"].ToString() == "MultiChoices" || dtPri.Rows[i]["control_type"].ToString() == "Free MultiChoices" || dtPri.Rows[i]["control_type"].ToString() == "Free List")
{
Control ctr = GetControlByName(frm, dtPri.Rows[i]["en_name"].ToString());
if (ctr != null)
{
ctr.Text = dtPri.Rows[i]["real_value"].ToString();
}
//}
}
}
}
}
private void GetData(string strPkid)
{
btnNew_Click(null, null);
dgvJourDetails.Rows.Clear();
ConnectionToMySQL cnn = new ConnectionToMySQL();
DataTable dtJournalData = cnn.GetDataTable("SELECT h.Pkid, h.stat, Branch_id, jour_no, trans_name, trans_id, date_format(jour_date,'%d/%m/%Y') jour_date, jour_note,Person,trans_no, " +
" d.pkid dpkid, d.stat dstat, curr_id,(select c.curr_name from currency c where c.pkid=d.curr_id) curr_name, acc_id, main_value, jour_value, exchange_Rate, jour_details,profitCenter,profitCenter1, " +
" a.acc_no, a.acc_name " +
" FROM journal_header h " +
" join journal_details d " +
" join accounts a on(a.pkid = d.acc_id) " +
" on(h.pkid = d.header_id) " +
" where h.pkid = " + strPkid + " order by d.pkid");
bLoad = true;
txtPkid.Text = strPkid;
txtJourNo.Text = dtJournalData.Rows[0]["trans_no"].ToString();
dtpJourDate.Value = DateTime.ParseExact(dtJournalData.Rows[0]["jour_date"].ToString(), "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
txtHeaderNote.Text = dtJournalData.Rows[0]["jour_note"].ToString();
txtPerson.Text = dtJournalData.Rows[0]["Person"].ToString();
txtDeptTotal.Text = "0";
for (int i = 0; i < dtJournalData.Rows.Count; i++)
{
if (Convert.ToDouble(dtJournalData.Rows[i]["main_value"].ToString()) > 0)
{
dgvJourDetails.Rows.Add();
dgvJourDetails[clmDept.Index, dgvJourDetails.Rows.Count - 1].Value = Convert.ToDecimal(dtJournalData.Rows[i]["main_value"].ToString()).ToString("###,###,###,##0.##");
}
else
{
continue;
}
dgvJourDetails[clmPKid.Index, dgvJourDetails.Rows.Count - 1].Value = Convert.ToDecimal(dtJournalData.Rows[i]["dpkid"].ToString()).ToString("###,###,###,##0.##");
dgvJourDetails[clmAccId.Index, dgvJourDetails.Rows.Count - 1].Value = dtJournalData.Rows[i]["acc_id"].ToString();
dgvJourDetails[clmAccNo.Index, dgvJourDetails.Rows.Count - 1].Value = dtJournalData.Rows[i]["acc_no"].ToString();
dgvJourDetails[clmAccName.Index, dgvJourDetails.Rows.Count - 1].Value = dtJournalData.Rows[i]["acc_name"].ToString();
dgvJourDetails[clmJourNote.Index, dgvJourDetails.Rows.Count - 1].Value = dtJournalData.Rows[i]["jour_details"].ToString();
dgvJourDetails[clmProfitCenter.Index, dgvJourDetails.Rows.Count - 1].Value = dtJournalData.Rows[i]["profitCenter"].ToString();
dgvJourDetails[clmProfitCenter1.Index, dgvJourDetails.Rows.Count - 1].Value = dtJournalData.Rows[i]["profitCenter1"].ToString();
txtDeptTotal.Text = (Convert.ToDouble(txtDeptTotal.Text.Trim()) + Convert.ToDouble(dgvJourDetails[clmDept.Index, dgvJourDetails.Rows.Count - 1].Value)).ToString("###,###,###,##0.##");
}
if (UserTemplate.HasPrivilege("btnUpdate"))
{
btnUpdate.Enabled = true;
}
if (UserTemplate.HasPrivilege("btnPrint"))
{
btnPrint.Enabled = true;
}
btnSave.Enabled = false;
bLoad = false;
dgvJourDetails.Rows.Add();
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (!CheckEntries())
{
return;
}
ConnectionToMySQL cnnSave = new ConnectionToMySQL();
DataTable dt = cnnSave.GetDataTable("(select ifnull(max(b.pkid),0)+1 from stockadjust_header b)");
txtPkid.Text = dt.Rows[0][0].ToString();
dt = cnnSave.GetDataTable("(select ifnull(max(b.stockadj_no),0)+1 from stockadjust_header b where branch_id=" + lstBranches.SelectedValue.ToString() + " and warehosue_id=" + lstWarehouses.SelectedValue.ToString() + ")");
txtStockAdjNo.Text = dt.Rows[0][0].ToString();
int icheck = cnnSave.TranDataToDB("insert into stockadjust_header " +
" values(" + txtPkid.Text + "" +
",'فعال'" +
",SYSDATE() " +
"," + glb_function.glb_strUserId +
"," + txtStockAdjNo.Text +
"," + lstBranches.SelectedValue.ToString() +
"," + lstWarehouses.SelectedValue.ToString() +
",'" + txtStockAdjNote.Text.Trim() + "'" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات التعديل المخزني");
return;
}
for (int i = 0; i < dgvStockAdjustment.Items.Count; i++)
{
icheck = cnnSave.TranDataToDB("insert into stockadjust_details values ((select ifnull(max(b.pkid),0)+1 from stockadjust_details b)" +
"," + txtPkid.Text +
"," + glb_function.GetCellValue(ref dgvStockAdjustment, clmItemId.DisplayIndex, i) +
"," + glb_function.GetCellValue(ref dgvStockAdjustment, clmRequiredQty.DisplayIndex, i) +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات الصنف");
return;
}
icheck = cnnSave.TranDataToDB("insert into item_trans values ((select ifnull(max(b.pkid),0)+1 from item_trans b)" +
",SYSDATE() " +
"," + glb_function.glb_strUserId +
"," + glb_function.GetCellValue(ref dgvStockAdjustment, clmItemId.DisplayIndex, i) +
"," + lstWarehouses.SelectedValue.ToString() +
"," + glb_function.GetCellValue(ref dgvStockAdjustment, clmRequiredQty.DisplayIndex, i) +
"," + txtPkid.Text +
",'تعديل مخزني'" +
",''" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات حركة الاصناف");
return;
}
}
cnnSave.glb_commitTransaction();
glb_function.MsgBox("تمت العملية بنجاح");
GetData(txtPkid.Text);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (!CheckEntries())
{
return;
}
ConnectionToMySQL cnn = new ConnectionToMySQL();
int icheck = 0;
icheck = cnn.TranDataToDB("update journal_header set " +
" created_user="******",jour_date=str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",jour_note='" + txtHeaderNote.Text.Trim() + "'" +
",person='" + txtPerson.Text.Trim() + "'" +
" where pkid=" + txtPkid.Text);
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء تعديل البيانات الأساسية");
return;
}
icheck = cnn.TranDataToDB("delete from journal_details where header_id= " + txtPkid.Text);
if (icheck <= 0)
{
cnn.glb_commitTransaction();
glb_function.MsgBox("حدث خطأ اثناء حذف البيانات السابقة");
return;
}
//حفظ بيانات الدائن
//يكون الصندوق دائن في سند الصرف ويميزه انه اصغر من الصفر
double dDept = Convert.ToDouble(txtDeptTotal.Text) * -1;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + glb_function.glb_iMainCurrId +
"," + strCasherAccId +
"," + dDept +
"," + dDept +
",1" +
",'" + txtHeaderNote.Text.Trim() + "'" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
for (int i = 0; i < dgvJourDetails.Rows.Count; i++)
{
if (dgvJourDetails[clmAccId.Index, i].Value == null || dgvJourDetails[clmAccId.Index, i].Value.ToString() == "")
{
continue;
}
double dMainValue = 0;
double dCurrValue = 0;
if (Convert.ToDouble(dgvJourDetails[clmDept.Index, i].Value.ToString()) > 0)
{
dMainValue = Convert.ToDouble(dgvJourDetails[clmDept.Index, i].Value.ToString());
dCurrValue = dMainValue;
}
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + glb_function.glb_iMainCurrId +
"," + dgvJourDetails[clmAccId.Index, i].Value.ToString() +
"," + dMainValue +
"," + dCurrValue +
",1" +
",'" + dgvJourDetails[clmJourNote.Index, i].Value.ToString() + "'" +
",'" + dgvJourDetails[clmProfitCenter.Index, i].Value.ToString() + "'" +
",'" + dgvJourDetails[clmProfitCenter1.Index, i].Value.ToString() + "'" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
}
cnn.glb_commitTransaction();
GetData(txtPkid.Text);
glb_function.MsgBox("تمت عملية الحفظ بنجاح");
}
private void btnPrint_Click(object sender, EventArgs e)
{
cryRepTrialBalance report = new cryRepTrialBalance();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
string strDate = "";
string strPrevDate = "";
string strStat = "";
if (ckbPosting.Checked == true)
{
strStat = " and h.stat in ('فعال', 'مرحل')";
}
else
{
strStat = " and h.stat in ( 'مرحل')";
}
if (ckbClosingEntry.Checked == false)
{
strStat += " and h.trans_name!='سند اقفال'";
}
if (ckbSelectDate.Checked == true)
{
strDate = " and h.trans_name!='قيد افتتاحي' and jour_date between str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpTo.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
if (dtpFrom.Value.ToString("dd/MM") == "01/01")
{
strPrevDate = " and h.trans_name='قيد افتتاحي'";
}
else
{
strPrevDate = " and jour_date between str_to_date('01/01/" + dtpFrom.Value.ToString("yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpFrom.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
}
}
if (strPrevDate == "")
{
// strPrevDate = " and jour_date between str_to_date('01/01/1999', '%d/%m/%Y') and str_to_date('02/01/1999', '%d/%m/%Y')";
strDate = " and h.trans_name!='قيد افتتاحي'";
strPrevDate = " and h.trans_name='قيد افتتاحي'";
}
dtReport.Clear();
//dtReport = cnn.GetDataTable("SELECT Acc_no,acc_name ," +
// "(select sum(main_value) " +
// " from journal_header h " +
// " join journal_details d on (d.header_id = h.Pkid) " +
// " join accounts a on (a.pkid = d.acc_id) " +
// " where main_value > 0 " +
// " and acc_no like concat(acc.acc_no, '%') " + strDate + strStat + ") dept, " +
// "(select sum(main_value) " +
// " from journal_header h " +
// " join journal_details d on (d.header_id = h.Pkid) " +
// " join accounts a on (a.pkid = d.acc_id) " +
// " where main_value > 0 " +
// " and acc_no like concat(acc.acc_no, '%') " + strPrevDate + strStat + ") Prevdept, " +
// " (select sum(main_value) * -1 " +
// " from journal_header h " +
// " join journal_details d on (d.header_id = h.Pkid) " +
// " join accounts a on (a.pkid = d.acc_id) " +
// " where main_value < 0 " +
// " and acc_no like concat(acc.acc_no,'%')" + strDate + strStat + ") credit, " +
// " (select sum(main_value) * -1 " +
// " from journal_header h " +
// " join journal_details d on (d.header_id = h.Pkid) " +
// " join accounts a on (a.pkid = d.acc_id) " +
// " where main_value < 0 " +
// " and acc_no like concat(acc.acc_no,'%')" + strPrevDate + strStat + ") Prevcredit, " +
// " 0.00 balance_dept,0.00 balance_credit " +
// " FROM accounts acc " +
// " order by acc_no desc");
dtReport = cnn.GetDataTable("select a.pkid,acc_no,acc_name ,a.parent_id, sum(if (d.main_value > 0 " + strPrevDate + strStat + ",d.main_value,0)) Prevdept, " +
" sum( if (d.main_value < 0 " + strPrevDate + strStat + ",d.main_value *-1,0)) Prevcredit, " +
" sum(if (d.main_value > 0 " + strDate + strStat + ", d.main_value,0)) Dept,sum(if (d.main_value < 0 " + strDate + strStat + ",d.main_value*-1,0) ) credit," +
" 0.00 balance_dept,0.00 balance_credit " +
"from journal_header h " +
" join journal_details d on(h.Pkid = d.header_id) " +
" join accounts a on(a.pkid = d.acc_id) " +
" group by a.pkid,acc_no,acc_name ,a.parent_id " +
" order by acc_no " +
"");
decimal dDept = 0;
decimal dCredit = 0;
decimal dPreDept = 0;
decimal dPreCredit = 0;
decimal dBalance = 0;
decimal dTotalDept = 0;
decimal dTotalCredit = 0;
decimal dTotalPreDept = 0;
decimal dTotalPreCredit = 0;
decimal dTotalBalanceDept = 0;
decimal dTotalBalanceCredit = 0;
for (int i = 0; i < dtReport.Rows.Count; i++)
{
if (dtReport.Rows[i]["dept"].ToString() == "")
{
dDept = 0;
dtReport.Rows[i]["dept"] = "0";
}
else
{
dDept = Convert.ToDecimal(dtReport.Rows[i]["dept"].ToString());
}
if (dtReport.Rows[i]["Prevdept"].ToString() == "")
{
dtReport.Rows[i]["Prevdept"] = "0";
dPreDept = 0;
}
else
{
dPreDept = Convert.ToDecimal(dtReport.Rows[i]["Prevdept"].ToString());
}
if (dtReport.Rows[i]["credit"].ToString() == "")
{
dCredit = 0;
dtReport.Rows[i]["credit"] = "0";
}
else
{
dCredit = Convert.ToDecimal(dtReport.Rows[i]["credit"].ToString());
}
if (dtReport.Rows[i]["Prevcredit"].ToString() == "")
{
dtReport.Rows[i]["Prevcredit"] = "0";
dPreCredit = 0;
}
else
{
dPreCredit = Convert.ToDecimal(dtReport.Rows[i]["Prevcredit"].ToString());
}
string stracc = dtReport.Rows[i]["acc_no"].ToString();
// if ((dDept + dPreDept) - (dCredit + dPreCredit) == 0 )
// if ((dDept- dPreDept)==0 && (dCredit- dPreCredit )== 0)
if ((dDept - dCredit) == 0 && (dPreDept - dPreCredit) == 0)
{
dtReport.Rows.Remove(dtReport.Rows[i]);
i--;
}
else
{
dBalance = (dDept + dPreDept) - (dCredit + dPreCredit);
if (dBalance < 0)
{
dtReport.Rows[i]["balance_credit"] = (dBalance * -1).ToString();
}
else
{
dtReport.Rows[i]["balance_dept"] = (dBalance).ToString();
}
}
}
DataTable dtMainAcc = cnn.GetDataTable("select pkid,acc_no,acc_name ,a.parent_id " +
" from accounts a " +
" where a.level < 5 " +
" order by Acc_no desc");
decimal sumDept = 0;
decimal sumCredit = 0;
decimal sumPrevDept = 0;
decimal sumPrevCredit = 0;
decimal sumBalDept = 0;
decimal sumBalCredit = 0;
int icount = 0;
int iReportCount = dtReport.Rows.Count;
decimal dBalanceTotal = 0;
for (int i = 0; i < dtMainAcc.Rows.Count; i++)
{
sumDept = 0;
sumCredit = 0;
sumPrevDept = 0;
sumPrevCredit = 0;
sumBalDept = 0;
sumBalCredit = 0;
icount = 0;
for (int j = 0; j < iReportCount; j++)
{
if (dtMainAcc.Rows[i]["pkid"].ToString() == dtReport.Rows[j]["parent_id"].ToString())
{
sumDept = sumDept + Convert.ToDecimal(dtReport.Rows[j]["Dept"].ToString());
sumCredit = sumCredit + Convert.ToDecimal(dtReport.Rows[j]["credit"].ToString());
sumPrevDept = sumPrevDept + Convert.ToDecimal(dtReport.Rows[j]["Prevdept"].ToString());
sumPrevCredit = sumPrevCredit + Convert.ToDecimal(dtReport.Rows[j]["Prevcredit"].ToString());
decimal dSumBalanc = (sumDept + sumPrevDept) - (sumCredit + sumPrevCredit);
if (dSumBalanc > 0)
{
sumBalDept = dSumBalanc;
sumBalCredit = 0;
}
else
{
sumBalDept = 0;
sumBalCredit = dSumBalanc * -1;
}
icount = 1;
}
}
if (icount <= 0)
{
continue;
}
DataRow repRow = dtReport.NewRow();
repRow["pkid"] = dtMainAcc.Rows[i]["pkid"].ToString();
repRow["acc_no"] = dtMainAcc.Rows[i]["acc_no"].ToString();
repRow["acc_name"] = dtMainAcc.Rows[i]["acc_name"].ToString();
if (dtMainAcc.Rows[i]["parent_id"].ToString() == "" || dtMainAcc.Rows[i]["parent_id"].ToString() == "0")
{
repRow["parent_id"] = "0";
dTotalDept = dTotalDept + sumDept;
dTotalCredit = dTotalCredit + sumCredit;
dTotalPreDept = dTotalPreDept + sumPrevDept;
dTotalPreCredit = dTotalPreCredit + sumPrevCredit;
dTotalBalanceDept = dTotalBalanceDept + sumBalDept;
dTotalBalanceCredit = dTotalBalanceCredit + sumBalCredit;
}
else
{
repRow["parent_id"] = dtMainAcc.Rows[i]["parent_id"].ToString();
}
repRow["Dept"] = sumDept;
repRow["credit"] = sumCredit;
repRow["Prevdept"] = sumPrevDept;
repRow["Prevcredit"] = sumPrevCredit;
dBalanceTotal = (sumDept + sumPrevDept) - (sumCredit + sumPrevCredit);
if (dBalanceTotal < 0)
{
repRow["balance_credit"] = (dBalanceTotal * -1).ToString();
repRow["balance_dept"] = "0";
}
else
{
repRow["balance_dept"] = (dBalanceTotal).ToString();
repRow["balance_credit"] = "0";
}
dtReport.Rows.Add(repRow);
iReportCount = iReportCount + 1;
}
DataView dv = dtReport.DefaultView;
dv.Sort = "acc_no";
DataTable sortedDT = dv.ToTable();
report.SetDataSource(sortedDT);
report.SetParameterValue("From", (ckbSelectDate.Checked == false ? "" : dtpFrom.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("To", (ckbSelectDate.Checked == false ? DateTime.Now.ToString("dd/MM/yyyy") : dtpTo.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("dTotalDept", dTotalDept.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalCredit", dTotalCredit.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalPreDept", dTotalPreDept.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalPreCredit", dTotalPreCredit.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalBalanceDept", dTotalBalanceDept.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalBalanceCredit", dTotalBalanceCredit.ToString("###,###,###,###.##"));
// report.SetParameterValue("strValueAlpha", new ConvertNumbersToArabicAlphabet(txtCreditTotal.Text).GetNumberAr());
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnUpdate_Click(object sender, RoutedEventArgs e)
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
int icheck = 0;
icheck = cnn.TranDataToDB("update journal_header set " +
"created_user="******",jour_date=str_to_date('" + dtpJourDate.SelectedDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",jour_note='" + txtHeaderNote.Text.Trim() + "'" +
",Person='" + txtPerson.Text.Trim() + "'" +
" where pkid=" + txtPkid.Text);
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء تعديل البيانات الأساسية");
return;
}
icheck = cnn.TranDataToDB("delete from journal_details where header_id= " + txtPkid.Text);
if (icheck <= 0)
{
cnn.glb_commitTransaction();
glb_function.MsgBox("حدث خطأ اثناء حذف البيانات السابقة");
return;
}
//حفظ بيانات المدين
//يكون الصندوق مدين في سند القبض ويميزه انه اكبر من الصفر
double dMainValue = (double)nmbDept.Value;
double dCurrValue = (double)nmbCurrValue.Value;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + lstCurrency.SelectedValue.ToString() +
"," + lstAccNo.SelectedValue.ToString() +
"," + dMainValue +
"," + dCurrValue +
"," + nmbExchangeRate.Value.ToString() +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء تعديل التفاصيل");
return;
}
//حفظ بيانات الدائن
//يكون الصندوق الدائن في سند القبض ويميزه انه اصغر من الصفر
dMainValue = (double)nmbDept.Value * -1;
dCurrValue = (double)nmbCurrValue.Value * -1;
icheck = cnn.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + txtPkid.Text +
"," + lstCurrency.SelectedValue.ToString() +
"," + strCasherAccId +
"," + dMainValue +
"," + dCurrValue +
"," + nmbExchangeRate.Value.ToString() +
",''" +
")");
if (icheck <= 0)
{
cnn.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء تعديل التفاصيل");
return;
}
cnn.glb_commitTransaction();
GetData(txtPkid.Text);
if (glb_function.MsgBox("تمت عملية التعديل بنجاح" + "\n" + "هل تريد طباعة سند القبض؟", "", true) == false)
{
return;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!CheckEntries())
{
return;
}
ConnectionToMySQL cnnSave = new ConnectionToMySQL();
DataTable dt = cnnSave.GetDataTable("(select ifnull(max(b.pkid),0)+1 from stockadjust_header b)");
txtPkid.Text = dt.Rows[0][0].ToString();
dt = cnnSave.GetDataTable("select warehouse_no from warehouse where pkid=" + lstWarehouses.SelectedValue.ToString());
string strWarehouseNo = dt.Rows[0][0].ToString();
// dt = cnnSave.GetDataTable("(select ifnull(max(b.stockadj_no),0)+1 from stockadjust_header b where branch_id="+glb_function.glb_strBranchPkid+" and warehosue_id="+lstWarehouses.SelectedValue.ToString()+ " and stackadj_type='"+strStackadj_type+"')");
dt = cnnSave.GetDataTable("(select ifnull(max(convert(substring( substring(stockadj_no,instr(stockadj_no, '-')+1),instr( substring(stockadj_no,instr(stockadj_no, '-')+1),'-')+1), signed)), 0) + 1 from stockadjust_header b where branch_id=" + glb_function.glb_strBranchPkid + " and warehosue_id=" + lstWarehouses.SelectedValue.ToString() + " and stackadj_type='" + strStackadj_type + "')");
txtStockAdjNo.Text = glb_function.glb_strBranchPkid + "-" + strWarehouseNo + "-" + dt.Rows[0][0].ToString();
int icheck = cnnSave.TranDataToDB("insert into stockadjust_header " +
" values(" + txtPkid.Text + "" +
",'فعال'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
"," + glb_function.glb_strUserId +
",'" + txtStockAdjNo.Text + "'" +
",'" + strStackadj_type + "'" +
"," + glb_function.glb_strBranchPkid +
"," + lstWarehouses.SelectedValue.ToString() +
",'" + txtStockAdjNote.Text.Trim() + "'" +
"," + lstAccNo.SelectedValue.ToString() +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات التعديل المخزني");
return;
}
for (int i = 0; i < dgvStockAdjustment.Rows.Count; i++)
{
icheck = cnnSave.TranDataToDB("insert into stockadjust_details values ((select ifnull(max(b.pkid),0)+1 from stockadjust_details b)" +
"," + txtPkid.Text +
"," + dgvStockAdjustment[clmItemId.Index, i].Value.ToString() +
"," + dgvStockAdjustment[clmRequiredQty.Index, i].Value.ToString() +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات الصنف");
return;
}
double dQty = 0;
if (strStackadj_type == "امر توريد")
{
dQty = Convert.ToDouble(dgvStockAdjustment[clmRequiredQty.Index, i].Value.ToString());
double dOldunitCost = Convert.ToDouble(dgvStockAdjustment[clmOldUnitCost.Index, i].Value.ToString());
double dQtyonhand = Convert.ToDouble(dgvStockAdjustment[clmQtyOnhand.Index, i].Value.ToString());
double dRequiredQty = Convert.ToDouble(dgvStockAdjustment[clmRequiredQty.Index, i].Value.ToString());
double dUnitCost = Convert.ToDouble(dgvStockAdjustment[clmUnitCost.Index, i].Value.ToString());
double dNewCost;
if (dQtyonhand + dRequiredQty == 0)
{
dNewCost = dUnitCost;
}
else
{
dNewCost = ((dOldunitCost * dQtyonhand) + (dRequiredQty * dUnitCost)) / (dQtyonhand + dRequiredQty);
}
icheck = cnnSave.TranDataToDB("update items set unitcost=" + dNewCost.ToString() + " where pkid=" + dgvStockAdjustment[clmItemId.Index, i].Value.ToString());
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات حركة الاصناف");
return;
}
}
else
{
dQty = -1 * Convert.ToDouble(dgvStockAdjustment[clmRequiredQty.Index, i].Value.ToString());
}
//اضافة الكمية الى المخزون
icheck = cnnSave.TranDataToDB("insert into item_trans values ((select ifnull(max(b.pkid),0)+1 from item_trans b)" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + dgvStockAdjustment[clmItemId.Index, i].Value.ToString() +
"," + lstWarehouses.SelectedValue.ToString() +
"," + dQty +
"," + txtPkid.Text +
",'" + strStackadj_type + "'" +
",''" +
",'" + txtStockAdjNo.Text.Trim() + "'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء عملية حفظ بيانات حركة الاصناف");
return;
}
}
if (strStackadj_type == "امر توريد")
{
//تقييد عملية امر التوريد المخزني في الحسابات
#region InventoryOrderIn
DataTable dtKeys = cnnSave.GetDataTable("select (select ifnull(max( convert( substring(jour_no,instr(jour_no,'-')+1),signed)),0)+1 FROM journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " ) JourNo,(select ifnull(max(pkid),0)+1 from journal_header) pkid");
string strJPkid = dtKeys.Rows[0]["pkid"].ToString();
string strJourNo = glb_function.glb_strBranchPkid + "-" + dtKeys.Rows[0]["JourNo"].ToString();
icheck = 0;
icheck = cnnSave.TranDataToDB("insert into journal_header values (" + strJPkid +
",'فعال'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
"," + glb_function.glb_strUserId +
"," + glb_function.glb_strBranchPkid +
",'" + strJourNo.Trim() + "'" +
",'امر توريد'" +
"," + txtPkid.Text +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",'قيدت من امر التوريد المخزني'" +
",''" +
",'" + txtStockAdjNo.Text + "'" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء حفظ البيانات الأساسية");
return;
}
//حفظ بيانات المدين
//يكون المخزون مدين في امر التوريد ويميزه انه اكبر من الصفر
double dMainValue = (double)nmbTotal.Value;
double dCurrValue = (double)nmbTotal.Value;
icheck = cnnSave.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + strInventoryAccId +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
//حفظ بيانات الدائن
//
dMainValue = (double)nmbTotal.Value * -1;
dCurrValue = (double)nmbTotal.Value * -1;
icheck = cnnSave.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + lstAccNo.SelectedValue.ToString() +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
#endregion
}
else
{
//تقييد عملية امر الصرف المخزني في الحسابات
#region InventoryOrderOut
DataTable dtKeys = cnnSave.GetDataTable("select (select ifnull(max( convert( substring(jour_no,instr(jour_no,'-')+1),signed)),0)+1 FROM journal_header where Branch_id=" + glb_function.glb_strBranchPkid + " ) JourNo,(select ifnull(max(pkid),0)+1 from journal_header) pkid");
string strJPkid = dtKeys.Rows[0]["pkid"].ToString();
string strJournalNo = glb_function.glb_strBranchPkid + "-" + dtKeys.Rows[0]["JourNo"].ToString();
icheck = 0;
icheck = cnnSave.TranDataToDB("insert into journal_header values (" + strJPkid +
",'فعال'" +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
"," + glb_function.glb_strUserId +
"," + glb_function.glb_strBranchPkid +
",'" + strJournalNo.Trim() + "'" +
",'امر صرف'" +
"," + txtPkid.Text +
",str_to_date('" + dtpJourDate.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y')" +
",'قيدت من امر الصرف المخزني'" +
",''" +
",'" + txtStockAdjNo.Text + "'" +
")");
if (icheck <= 0)
{
glb_function.MsgBox("حدث خطأ اثناء حفظ البيانات الأساسية");
return;
}
//حفظ بيانات المدين
//
double dMainValue = (double)nmbTotal.Value;
double dCurrValue = (double)nmbTotal.Value;
icheck = cnnSave.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + lstAccNo.SelectedValue.ToString() +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
//حفظ بيانات الدائن
//يكون الصندوق دائن في سند الصرف ويميزه انه اصغر من الصفر
dMainValue = (double)nmbTotal.Value * -1;
dCurrValue = (double)nmbTotal.Value * -1;
icheck = cnnSave.TranDataToDB("insert into journal_details values ((select ifnull(max(b.pkid),0)+1 from journal_details b )" +
",'فعال'" +
",sysdate()" +
"," + glb_function.glb_strUserId +
"," + strJPkid +
"," + glb_function.glb_iMainCurrId +
"," + strInventoryAccId +
"," + dMainValue +
"," + dCurrValue +
",1" +
",''" +
",''" +
",''" +
")");
if (icheck <= 0)
{
cnnSave.glb_RollbackTransaction();
glb_function.MsgBox("حدث خطأ اثناء حفظ التفاصيل");
return;
}
#endregion
}
cnnSave.glb_commitTransaction();
glb_function.MsgBox("تمت العملية بنجاح");
GetData(txtPkid.Text);
}
