public List <string> syncColumnValues(DaftarTable daftar, Int64 syncValue)
{
try
{
DaftarModelDataContext db = new DaftarModelDataContext(cn);
var data = from a in db.DaftarTables.Where(x => x.Id == daftar.Id) select a;
List <string> list = new List <string>();
list.Add("شناسه های ");
foreach (var a in data)
{
a.DepositDetail = daftar.DepositDetail;
a.CodeBudget = daftar.CodeBudget;
list.Add(a.Id.ToString() + " " + a.DepositOwnerDetail.ToString() + " با موفقیت بروزرسانی شد ");
}
db.SubmitChanges();
return(list);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
List <string> list = new List <string>();
Reporter rpt = new Reporter(list, (int)errorImages.info);
rpt.Show();
return(list);
}
}
public List <String> addRow(DaftarTable daftar)
{
try
{
List <string> report = new List <string>();
DaftarModelDataContext db = new DaftarModelDataContext(cn);
db.DaftarTables.InsertOnSubmit(daftar);
db.SubmitChanges();
string person = daftar.Id.ToString() + " " + daftar.DepositOwnerDetail.ToString();
report.Add(" با موفقیت اضافه گردید" + " " + person);
return(report);
}
//duplicate record
catch (System.Data.SqlClient.SqlException)
{
// MessageBox.Show(ex.ToString());
// DaftarModelDataContext db = new DaftarModelDataContext(cn);
List <string> report = new List <string>();
report.Add(daftar.Id + "" + " شناسه تکراری است . تاریخ یا کد سپرده می بایست متفاوت باشد");
return(report);
}
//catch (Exception ex)
//{
// //MessageBox.Show(ex.Message);
// return false;
//}
}
public Result billDetailCodeQuery(long cityID, string code, StartEndMonthClass startend, int pageSize, mode searchType)
{
DaftarModelDataContext db = new DaftarModelDataContext(cn);
int depositValue = -1;
int refundValue = -1;
switch (searchType)
{
case mode.all:
depositValue = -1;
refundValue = -1;
break;
case mode.deposit:
depositValue = -1;
refundValue = 0;
break;
case mode.refund:
depositValue = 0;
refundValue = -1;
break;
}
Result result = new Result();
if (cityID == 0)
{
var query = (from c in db.DaftarTables.Where(x => x.RealDate >= startend.startDate &&
x.RealDate <= startend.endDate && x.BillDetailCode.ToString().Contains(code) &&
x.Deposit.Value > depositValue && x.Refund.Value > refundValue)
select c);
var count = query.Count();
result.recordCount = count;
result.queryPageNumber = count / pageSize;
result.query = query;
return(result);
}
var query2 = (from c in db.DaftarTables.Where(x => x.CityID == cityID &&
x.RealDate >= startend.startDate &&
x.RealDate <= startend.endDate &&
x.BillDetailCode.ToString().Contains(code) &&
x.Deposit.Value > depositValue && x.Refund.Value > refundValue)
select c);
var count2 = query2.Count();
result.recordCount = count2;
result.queryPageNumber = count2 / pageSize;
result.query = query2;
return(result);
}
public List <string> remove(List <DaftarTable> newDaftar, bool godmode, bool permision)
{
IQueryable <DaftarTable> data;
data = null;
DaftarModelDataContext db = new DaftarModelDataContext(cn);
List <string> report = new List <string>();
report.Add("لیست زیر");
int count = 0;
foreach (var daftar in newDaftar)
{
count++;
data = from a in db.DaftarTables.Where(x => x.Id == daftar.Id) select a;
foreach (var a in data)
{
if (Math.Abs(Convert.ToInt64(a.Refund)) > 0 && !godmode)
{
report.Add(a.Id.ToString() + " " + a.DepositOwnerDetail.ToString() + " استرداد شده است! غیر قابل ویرایش می باشد ");
}
else
{
if (isRestricted(daftar, permision))
{
string msg = " دسترسی شما به این تاریخ توسط مدیر سیستم محدود شده است";
report.Add(a.Id + " " + a.DepositOwnerDetail + " " + msg);
}
else
{
db.DaftarTables.DeleteOnSubmit(a);
string msg = " حذف گردید ";
report.Add(a.Id + " " + a.DepositOwnerDetail + " " + msg);
}
}
}
}
try
{
db.SubmitChanges();
return(report);
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
report.Add("خطا در حذف");
return(report);
}
}
public List <string> update(DaftarTable daftar, bool godmode)
{
try
{
DaftarModelDataContext db = new DaftarModelDataContext(cn);
var data = from a in db.DaftarTables.Where(x => x.Id == daftar.Id) select a;
List <string> list = new List <string>();
list.Add("شناسه های ");
foreach (var a in data)
{
if ((Math.Abs(Convert.ToInt64(a.Refund))) > 0 && !godmode)
{
list.Add(a.Id.ToString() + " " + a.DepositOwnerDetail.ToString() + " استرداد شده است! غیر قابل ویرایش می باشد ");
}
else
{
a.Id = daftar.Id;
//a.PlaceName = daftar.PlaceName;
//a.CityID = daftar.CityID;
a.Refund = daftar.Refund;
//a.Date = daftar.Date;
a.Deposit = daftar.Deposit;
a.DepositDetail = daftar.DepositDetail;
a.DepositOwnerDetail = daftar.DepositOwnerDetail;
a.CodeBudget = daftar.CodeBudget;
a.AccountType = daftar.AccountType;
// a.RealDate = daftar.RealDate;
// a.BillDetailCode = daftar.BillDetailCode;
list.Add(a.Id.ToString() + " " + a.DepositOwnerDetail.ToString() + " با موفقیت بروزرسانی شد ");
}
}
db.SubmitChanges();
return(list);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
List <string> list = new List <string>();
Reporter rpt = new Reporter(list, (int)errorImages.info);
rpt.Show();
return(list);
}
}
public List <string> refund(List <DaftarTable> newDaftar, bool godmode)
{
IQueryable <DaftarTable> data;
data = null;
DaftarModelDataContext db = new DaftarModelDataContext(cn);
List <string> report = new List <string>();
foreach (var daftar in newDaftar)
{
data = from a in db.DaftarTables.Where(x => x.Id == daftar.Id) select a;
foreach (var a in data)
{
//if(isRestricted(a))
//{
// report.Add(a.Id.ToString() + " " + a.DepositOwnerDetail + " " + "دسترسی توسط مدیر سیستم محدود شده است ");
//}else if
//
if (a.Deposit != 0)
{
a.Refund = a.Deposit;
a.Deposit = 0;
report.Add(a.Id.ToString() + " " + a.DepositOwnerDetail + " " + "مسترد شد ");
}
else
{
report.Add(a.Id.ToString() + " " + a.DepositOwnerDetail + " " + "مبلغ قبلا مسترد شده است ");
}
}
}
try
{
db.SubmitChanges();
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
}
return(report);
}
public StartEndMonthClass findMinMaxInQuery(IQueryable <Daftarcs> data)
{
StartEndMonthClass minmax = new StartEndMonthClass();
try
{
DaftarModelDataContext db = new DaftarModelDataContext(cn);
var q = from row in data
group row by true into r
select new { min = r.Min(z => z.RealDate), max = r.Max(z => z.RealDate) };
foreach (var a in q)
{
minmax.endDate = a.max;
minmax.startDate = a.min;
}
return(minmax);
}
catch (Exception)
{
throw;
}
}
public MaxMinClass findMaxminMonthDay(long cityID)
{
MaxMinClass minmax = new MaxMinClass();
try
{
if (cityID == 0)
{
DaftarModelDataContext db = new DaftarModelDataContext(cn);
var q = from row in db.DaftarTables
group row by true into r
select new { min = r.Min(z => z.RealDate), max = r.Max(z => z.RealDate) };
foreach (var a in q)
{
minmax.max = a.max.Value;
minmax.min = a.min.Value;
}
return(minmax);
}
else
{
DaftarModelDataContext db = new DaftarModelDataContext(cn);
var p = from row in db.DaftarTables.Where(z => z.CityID == cityID)
group row by true into r
select new { min = r.Min(z => z.RealDate), max = r.Max(z => z.RealDate) };
foreach (var a in p)
{
minmax.max = a.max.Value;
minmax.min = a.min.Value;
}
return(minmax);
}
}catch (Exception)
{
throw;
}
}
public Boolean excelToSqlCopy(IQueryable <Daftarcs> excelQuery, long cityID)
{
try
{
Utility utl = new Utility();
DaftarModelDataContext db =
new DaftarModelDataContext(cn);
List <DaftarTable> listDaftar = new List <DaftarTable>();
foreach (var row in excelQuery)
{
DaftarTable newDaftar = new DaftarTable();
newDaftar.DepositOwnerDetail = row.DepositOwnerDetail;
newDaftar.PlaceName = row.PlaceName;
newDaftar.AccountType = row.AccountType;
newDaftar.Date = row.Date;
newDaftar.RealDate = utl.changeToRealDate(newDaftar.Date);
newDaftar.BillDetailCode = Convert.ToInt64(row.BillDetailCode);
newDaftar.CodeBudget = Convert.ToInt64(row.CodeBudget);
newDaftar.Deposit = Convert.ToInt64(row.Deposit);
newDaftar.DepositDetail = Convert.ToInt64(row.DepositDetail);
newDaftar.Refund = Convert.ToInt64(row.Refund);
newDaftar.Id = utl.idGenerator(cityID, newDaftar.Date, newDaftar.BillDetailCode);
newDaftar.CityID = cityID;
listDaftar.Add(newDaftar);
}
db.BulkInsertAll(listDaftar);
excelQuery = null;
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(false);
}
}
public List <string> refundFromFile(IQueryable <Daftarcs> excelQuery, long cityID)
{
try
{
List <string> report = new List <string>();
Utility utl = new Utility();
DaftarModelDataContext db =
new DaftarModelDataContext(cn);
List <DaftarTable> listDaftar = new List <DaftarTable>();
StartEndMonthClass date = findMinMaxInQuery(excelQuery);
foreach (var row in excelQuery)
{
DaftarTable newDaftar = new DaftarTable();
newDaftar.DepositOwnerDetail = row.DepositOwnerDetail;
newDaftar.PlaceName = row.PlaceName;
newDaftar.AccountType = row.AccountType;
newDaftar.Date = row.Date;
newDaftar.RealDate = utl.changeToRealDate(newDaftar.Date);
newDaftar.BillDetailCode = Convert.ToInt64(row.BillDetailCode);
newDaftar.CodeBudget = Convert.ToInt64(row.CodeBudget);
newDaftar.Deposit = Convert.ToInt64(row.Deposit);
newDaftar.DepositDetail = Convert.ToInt64(row.DepositDetail);
newDaftar.Refund = Convert.ToInt64(row.Refund);
newDaftar.Id = utl.idGenerator(cityID, newDaftar.Date, newDaftar.BillDetailCode);
newDaftar.CityID = cityID;
listDaftar.Add(newDaftar);
}
var query = listDaftar.AsQueryable();
var search = (from c in db.DaftarTables.Where(x => x.CityID == cityID && x.RealDate >= date.startDate && x.RealDate <= date.endDate) select c);
foreach (var b in query)
{
bool refunded = false;
foreach (var a in search)
{
if (a.Id == b.Id)
{
if (b.Refund != 0 && b.Deposit == 0 && a.Refund == 0)
{
a.Refund = a.Deposit;
a.Deposit = 0;
report.Add(a.DepositOwnerDetail + " " + a.Id + "استرداد شد");
refunded = true;
}
}
}
if (!refunded)
{
report.Add(b.DepositOwnerDetail + " " + b.Id + "خطا در استرداد");
}
}
db.SubmitChanges();
excelQuery = null;
return(report);
}
catch (Exception ex)
{
string exp = "خطا";
List <string> report = new List <string>();
report.Add(ex.ToString());
report.Add(exp);
return(report);
}
}